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
Please spread the word:
