Find a User's Effective Permissions

There was a recent post on the MSDN forums, that paraphrased, asked, how do I find out what permissions a User has to all Objects, at all levels, including those granted by their role memberships, etc.

Now, I am assuming that this is a fairly common security auditing task, so I scanned Google to dig out a script that would do it, but to my amazement, I could not find one, so I decided to write one. It is a pretty simple script, using the fn_my_permissions function and a couple of well known system tables.

In order to run it, you will need to be able to impersonate the login that you are running it for, and of course, you will also need the appropriate permissions to the system tables.

Feel free to modify the script, but please give due credit. This will work with 2005 and 2008, but I actually wrote it in Denali, so it is defiantly forward-compatible. Enjoy...

EXECUTE AS LOGIN = 'INSERT LOGIN NAME'
 SELECT t.name,  a.subentity_name, a.permission_name
 FROM sys.objects t
 CROSS APPLY fn_my_permissions(QUOTENAME(t.name), 'OBJECT') a
 UNION ALL
 SELECT d.name, a.subentity_name, a.permission_name
 FROM sys.databases d
 CROSS APPLY fn_my_permissions(QUOTENAME(d.name), 'DATABASE') a
 UNION ALL
 SELECT @@SERVERNAME, a.subentity_name, a.permission_name
 FROM fn_my_permissions(NULL, 'SERVER') a
 ORDER BY t.name
 REVERT


Find my book, Pro SQL Server Administration on Amazon -

America


United Kingdom