SQL Server - How to get user login mappings?

Question

I know this question has been asked before in many forms, but unfortunately, none of them helped me in this quite basic need - I'm looking for a script which takes as a parameters a particular login's name & database and return the appropriated associated roles.

For example - for the attached screen shot, when passing the script the ADMA1 user name & the fogbugzRelease database the output shoul be db_owner.

Answer 1

You can use below query to get database role assigned to the user

DECLARE @command nvarchar(4000) 
DECLARE @DBRole TABLE (DBName sysname, UserName sysname, RoleName varchar(255))
SELECT @command = 'use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') else prin.name end AS UserName
,isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id'

INSERT INTO @DBRole
EXEC sp_MSforeachdb @command 

SELECT * FROM @DBRole t 
WHERE t.UserName like '%YourUserName%'
and t.DBName like '%Your DB Name%'