Systems Engineering and RDBMS

Checking permissions for different logins in SQL Server 2005

Posted by decipherinfosys on December 29, 2008

SQL Server 2005 has two table valued functions called:

a) fn_my_permissions(securable, ‘securable_class’), and
b) has_perms_by_name(securable, securable_class, permission, sub-securable, sub-securable_class)

that can be used to check the different permissions of the users on a securable.  Securable is just term being used for the different objects like table, stored procedures, functions, views etc.. The securable_class are like these: ASSEMBLY, OBJECT, SERVER, DATABASE, XML Schema Collection etc.  You can look at all the securable classes by using this SQL:

SELECT distinct class_desc
FROM sys.fn_builtin_permissions(default)
ORDER BY class_desc
GO

And specifying the word default just translates the input to Null in these functions.  So, how and when can you make use of these functions?  These are helpful if you want to prepare a report for the permissions for different logins.  Here is an example:

Let’s see my login permissions first (my login is also part of the sysadmin role):

/*Show me permissions on the server*/

SELECT * FROM sys.fn_my_permissions(default, ‘SERVER’);

/*Now, show me permissions on the database*/

SELECT * FROM sys.fn_my_permissions(default, ‘DATABASE’);

/*Or a specific object*/

SELECT * FROM sys.fn_my_permissions(‘usp_collect_stats’, ‘OBJECT’);

And now, if I want to check the permissions for another login, all I need to do is switch the security context first by using “EXECUTE AS” and then execute the same commands as above:

EXECUTE AS LOGIN = ‘appuser’;
SELECT * FROM sys.fn_my_permissions(default, ‘DATABASE’);

And once I am done reviewing the output, I can use the “REVERT” command to revert back to my login for that session.

One can also obtain this information by making use of the catalog views:

select
dp.type_desc AS principal_type_desc,
dbp.class_desc,
OBJECT_NAME(dbp.major_id) AS object_name,
dbp.permission_name,
dbp.state_desc AS permission_state_desc
from    sys.database_permissions dbp
INNER JOIN sys.database_principals dp
on dbp.grantee_principal_id = dp.principal_id
where USER_NAME(dbp.grantee_principal_id) = ‘sysdba’

About these ads

3 Responses to “Checking permissions for different logins in SQL Server 2005”

  1. erjoell said

    The query against the sys.database_permissions only shows explicitly granted permissions. It does not show those derive permissions acquired as a result of role membership.

  2. sarmadh said

    Well explained!..

  3. Anonymous said

    very helpful. Thanls !

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 84 other followers

%d bloggers like this: