Get all DB users

Question

Post reply

Get all DB users

89netanel

SSCrazy

Points: 2130
More actions
August 10, 2017 at 7:21 am

#344066

Hello All,
I need help.
I need to generate data of all the DBs users on my instance and i need the desire columns:
DBName, User Name, Login Name, Login Type, Associated Role
Can someone help?
Thanks in advance!!!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

rvsc48 SSCertifiable Points: 7685 More actions · Answer 1

See if this script helps you -

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

89netanel SSCrazy Points: 2130 More actions · Answer 2

Hi RVSC48,
Thank you very much for your quick answer!
It's help me very much!!!
I just added a filter:

and prin.type_desc != ''DATABASE_ROLE''

because i dont need the DATABASE_ROLE.

Thanks!!!