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
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 🙂
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.