Mini Post: Showing Permissions Granted To A User, Via T-SQL

Hey everyone! Welcome back to SQL Something!

Today we are going to take a quick look at how to view the available permissions a user was granted. I needed this the other day when I wanted to create a user with similar permissions for another DB. Luckily fn_my_permissions has all the answers we need.

We can query fn_my_permissions to find out permissions info at a DB level like so:

USE YourDBName;
SELECT *
FROM fn_my_permissions (NULL, 'DATABASE');

This, however, will list the permissions for the current user doing the query (which might not actually be the user you want the info for).

To find permissions for a specific user, you must first impersonate that user like so:

USE YourDBName;
EXECUTE AS USER 'User1';
SELECT *
FROM fn_my_permissions (NULL, 'DATABASE');
REVERT;

Please note that using the EXECUTE command will give you only the permissions of the user you are impersonating. You need the REVERT command at the end of the query to give yourself back the permissions you previously had.

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)