question

amfineandyou avatar image
amfineandyou asked

SQL User and Permission

SQLites, I want to write a sql query to retieve user and permissions of a SQL Database.But what I have been assigned is to have the result set as below DBNAME USERName DB_Owner DB_reader etc etc etc abc xyz yes No efg bvc no Yes Can someone help me to get the SQL query.
sql-server-2008sqlsql server 2012permissionsusers
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sorry the format is not correct in the above: it is kind of table format with heading as: DBNAME USERName DB_Owner DB_reader etc etc etc and DBName = abc, UserName=xyz, DB_Owner=yes, DB_reader = No Like this, the result set should be in the table format and with Yes or no corresponding to the column of the user and permission that user has.
0 Likes 0 ·

1 Answer

· Write an Answer
rvsc49 avatar image
rvsc49 answered
See if this script will give you what you are looking for - 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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.