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.
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.
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
20 People are following this question.
