Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

If you are wondering what permissions a role has, and don’t want to spend time searching and digging through Books Online, there are a few system procedures that can help. These will give you the permissions for a role, or all roles.

Let’s see how to use these.

Server Role Permissions

Let’s say that you are wondering what a Processadmin can do. There is a procedure, sp_srvrrolepermission, that you can use to get the list of permissions. This can be run with no parameters, in which case you’ll get all roles and all permissions.

However, often you’ll want a more manageable set of data that a person can understand. Let’s see what processadmin can do.

Only two permissions here, so I can see that granting this isn’t going to affect security or databases.

Database Roles

The database has a related procedure, sp_dbfixedrolepermission, that looks for database roles. This can again be run without any parameters, as shown here.

If we include a role, we see just those permissions.

SQLNewBlogger

This is a short post, really about 5 minutes, based on my need to solve another issue. However, this is something a DBA should know, and it’s something I’ll likely use again.