T-SQL to list all the user mappings with database roles/permissions for a Login

Question

I am looking for a t-sql script which can list the databases and and the respective roles/privileges mapped for a particular user. Using SQL Server 2008 R2.

Solution 1

CREATE TABLE #tempww (
    LoginName nvarchar(max),
    DBname nvarchar(max),
    Username nvarchar(max), 
    AliasName nvarchar(max)
)

INSERT INTO #tempww 
EXEC master..sp_msloginmappings 

-- display results
SELECT * 
FROM   #tempww 
ORDER BY dbname, username

-- cleanup
DROP TABLE #tempww

Solution 2

CREATE TABLE #tempww (

    LoginName nvarchar(max),
    DBname nvarchar(max),
    Username nvarchar(max), 
    AliasName nvarchar(max)
)

INSERT INTO #tempww 

EXEC master..sp_msloginmappings 

-- display results

declare @col varchar(1000)

declare @sql varchar(2000)

select @col = COALESCE(@col + ', ','') + QUOTENAME(DBname)

from #tempww Group by DBname

Set @sql='select * from (select LoginName,Username,AliasName,DBname,row_number() over(order by (select 0)) rn from #tempww) src

PIVOT (Max(rn) FOR DBname

IN ('+@col+')) pvt'

EXEC(@sql)



-- cleanup
DROP TABLE #tempww

Solution 3

I wrote a little query to find permission of a user on a specific database.

    SELECT * FROM   
    (
    SELECT 
    perm.permission_name AS 'PERMISSION'
    ,perm.state_desc AS 'RIGHT'
    ,perm.class_desc AS 'RIGHT_ON'
    ,p.NAME AS 'GRANTEE'
    ,m.NAME AS 'USERNAME'
    ,s.name AS 'SCHEMA'
    ,o.name AS 'OBJECT'
    ,IIF(perm.class = 0, db_name(), NULL) AS 'DATABASE'
    FROM
    sys.database_permissions perm
    INNER JOIN sys.database_principals p ON p.principal_id = perm.grantee_principal_id
    LEFT JOIN sys.database_role_members rm ON rm.role_principal_id = p.principal_id
    LEFT JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
    LEFT JOIN sys.schemas s ON perm.class = 3 AND perm.major_id = s.schema_id
    LEFT JOIN sys.objects AS o ON perm.class = 1 AND perm.major_id = o.object_id
    UNION ALL
    SELECT 
    perm.permission_name AS 'PERMISSION'
    ,perm.state_desc AS 'RIGHT'
    ,perm.class_desc AS 'RIGHT_ON'
    ,'SELF-GRANTED' AS 'GRANTEE'
    ,p.NAME AS 'USERNAME'
    ,s.name AS 'SCHEMA'
    ,o.name AS 'OBJECT'
    ,IIF(perm.class = 0, db_name(), NULL) AS 'DATABASE'
    FROM
    sys.database_permissions perm
    INNER JOIN sys.database_principals p ON p.principal_id = perm.grantee_principal_id
    LEFT JOIN sys.schemas s ON perm.class = 3 AND perm.major_id = s.schema_id
    LEFT JOIN sys.objects AS o ON perm.class = 1 AND perm.major_id = o.object_id
    ) AS [union]
    WHERE [union].USERNAME = 'Username' -- Username you will search for
    ORDER BY [union].RIGHT_ON, [union].PERMISSION, [union].GRANTEE

The permissions of fixed database roles do not appear in sys.database_permissions. Therefore, database principals may have additional permissions not listed here.

I does not prefer

    EXECUTE AS USER = 'userName';
    SELECT * FROM fn_my_permissions(NULL, 'DATABASE') 

Because it's just retrieving which permissions the user has not where they come from!

Maybe i find out how to join the fixed database roles permission granted for the user one day...

Pls enjoy Life and hate the Users :D

Solution 4

Stole this from here. I found it very useful!

DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)

INSERT @DB_USers
EXEC sp_MSforeachdb

'
use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''

SELECT

dbname,username ,logintype ,create_date ,modify_date ,

STUFF(

(

SELECT ',' + CONVERT(VARCHAR(500),associatedrole)

FROM @DB_USers user2

WHERE

user1.DBName=user2.DBName AND user1.UserName=user2.UserName

FOR XML PATH('')

)

,1,1,'') AS Permissions_user

FROM @DB_USers user1

GROUP BY

dbname,username ,logintype ,create_date ,modify_date

ORDER BY DBName,username

View more solutions

Comments

[muddu83]

Thank you @Amon but I think I have not made myself clear. The output of the script should give me the list of the dbs, users mapped to the dbs and the database role memberships for each of them for a given login.

[muddu83]

Thanks @JDC. I am afraid that did not help me. To be precise what I am looking for is the same thing termed as 'Server roles' and 'User Mapping' under Login Properties for a particular login. I need to have the concerned server roles and user mapping details for a particular login listed out.

[JustJohn]

What is the number under some of the database columns? Pretty sure it is the database that the Login Name has permissions to but is it number of times this login has logged in?

[Ali Rasouli]

the number is incremental and order of access.

[RonJohn]

That only answers half the question, ignoring "and the respective roles/privileges mapped for a particular user".

[Pxtl]

If you do this with a table variable instead of a temp table you can skip the cleanup.