How to Verify Given Access/Permissions to User/Login

Hi Friends,

I have seen DBAs to wait for user’s confirmation for long after providing access/permissions. This is not required since we have very easy way to verify user permissions.

Let’s say I have provided EXECUTE permission for procedure [dbo].[whoAmI] to AD group [Contso\PPM_Dev] on database [Novice] of server SQL-A. So, in order to verify if given permission is working or not, we at first need to find out one member of the AD group. To accomplish that, we will use master..xp_logininfo.
Now, copy one of the AD group member, say [Contso\Rohan], and using EXECUTE AS T-SQL code, change the Session Context to user’s context. Below is screenshot for same:-

Now, one might say that it is not always possible to verify the given permissions since it’s not advisable to EXECUTE the procedure, or perform TRUNCATE etc. For those scenarios, we can use sys.fn_my_permissions function which returns a list of the permissions effectively granted to the principal on a securable. So, for this scenario, the final statement to verify the permission would be as below:-

Friends, please feel free to correct me by comments. If you like the article, do Like & Share. Happy Coding 🙂