Month: November 2018

Retrieve all permissions for a user in all user databases

by Dev on

<<Previous
The below query is to retrieve all permissions for a user in all user databases. You can either
copy the code or directly download from the link given below the code.

DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(MAX)

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','msdb','model','tempdb') 
AND state_desc='online' 
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN  

SELECT @statement = 'use ['+@dbname +'];'+ 'SELECT

 ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin, pp.name as PermissionLevel, pp.type_desc as TypeOfRole 

FROM sys.database_role_members roles

JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id

JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
where p.name=''Test_User''' -- Change the user name

EXEC sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

{ Add a Comment }

Query To Retrieve All Permissions For a User in Selective databases:

by Dev on

If you have directly landed here please read the article to understand what the query is for. The article also describes in what all scenarios you can use the query. Additionally please feel free to tweak it as per your requirement.

Query To Retrieve All Permissions For a User in Selective databases:
DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(MAX)

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM master.sys.databases
WHERE name IN ('database1','database2') --Obviously you’ll put your database names 
AND state_desc='online' 
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN  

SELECT @statement = 'use ['+@dbname +'];'+ 'SELECT

ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin, pp.name as PermissionLevel, pp.type_desc as TypeOfRole

FROM sys.database_role_members roles

JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id

JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
where p.name =''Test_User'''  --The user you want to find permissions for

EXEC sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

 

{ 9 Comments }