Systems Engineering and RDBMS

Archive for December 29th, 2008

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

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:

dp.type_desc AS principal_type_desc,
OBJECT_NAME(dbp.major_id) AS object_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’

Posted in SQL Server | 3 Comments »