Getting the Role Permissions–#SQLNewBlogger

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.

2018-07-03 14_46_01-SQLQuery9.sql - (local)_SQL2016.EmptyFileTest (PLATO_Steve (74))_ - Microsoft SQ

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

2018-07-03 14_45_47-SQLQuery9.sql - (local)_SQL2016.EmptyFileTest (PLATO_Steve (74))_ - Microsoft SQ

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.

2018-07-03 14_50_24-SQLQuery9.sql - (local)_SQL2016.EmptyFileTest (PLATO_Steve (74))_ - Microsoft SQ

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

2018-07-03 14_50_51-SQLQuery9.sql - (local)_SQL2016.EmptyFileTest (PLATO_Steve (74))_ - Microsoft SQ


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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.