(DIR) Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
 (HTM) https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
 (DIR) Return to: Scripts
       *****************************************************
       #Post#: 145--------------------------------------------------
       Llist all Usernames, Roles for all the databases
       By: srinivasma_exceldbp Date: February 10, 2015, 10:34 pm
       ---------------------------------------------------------
       /**
       Script: list all Usernames, Roles for all the databases.
       and the database Roles that the user belongs to in all the
       databases.
       Also, you can use this script to get the roles of one user in
       all the databases.
       Directions of Use:
       For All Users list: You can directly run this script in SQL
       Server Management studio
       For a specific user:
       1. Find this code and u.name like ''tester''
       2. Uncomment the code
       3. Replace the Name ''tester'' with the username you want to
       search on.
       Resultset:
       DBName: Database name that the user exists in.
       Name: user name.
       GroupName: Group/Database Role that the user is a part of.
       LoginName: Actual login name, if this is null, Name is used to
       connect.
       default_database_name
       default_schema_name
       principal_id
       sid
       **/
       USE MASTER
       GO
       set nocount on
       BEGIN
       DECLARE @SQLVerNo INT;
       SET @SQLVerNo =
       cast(substring(CAST(Serverproperty('ProductVersion') AS
       VARCHAR(50))
       ,0,charindex('.',CAST(Serverproperty('ProductVersion') AS
       VARCHAR(50)) ,0)) as int);
       IF @SQLVerNo >= 9
       IF EXISTS (SELECT TOP 1 *
       FROM Tempdb.sys.objects (nolock)
       WHERE name LIKE '#TUser%')
       DROP TABLE #TUser
       ELSE
       IF @SQLVerNo = 8
       BEGIN
       IF EXISTS (SELECT TOP 1 *
       FROM Tempdb.dbo.sysobjects (nolock)
       WHERE name LIKE '#TUser%')
       DROP TABLE #TUser
       END
       CREATE TABLE #TUser (
       ServerName    varchar(256),
       DBName        SYSNAME,
       [Name]        SYSNAME,
       GroupName     SYSNAME NULL,
       LoginName     SYSNAME NULL,
       default_database_name  SYSNAME NULL,
       default_schema_name    VARCHAR(256) NULL,
       Principal_id  INT,
       [sid]         VARBINARY(85))
       IF @SQLVerNo = 8
       BEGIN
       INSERT INTO #TUser
       EXEC sp_MSForEachdb
       '
       SELECT
       @@SERVERNAME,
       ''?'' as DBName,
       u.name As UserName,
       CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS
       GroupName,
       l.name AS LoginName,
       NULL AS Default_db_Name,
       NULL as default_Schema_name,
       u.uid,
       u.sid
       FROM [?].dbo.sysUsers u
       LEFT JOIN ([?].dbo.sysMembers m
       JOIN [?].dbo.sysUsers r
       ON m.groupuid = r.uid)
       ON m.memberuid = u.uid
       LEFT JOIN dbo.sysLogins l
       ON u.sid = l.sid
       WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1
       /*and u.name like ''tester''*/
       ORDER BY u.name
       '
       END
       ELSE
       IF @SQLVerNo >= 9
       BEGIN
       INSERT INTO #TUser
       EXEC sp_MSForEachdb
       '
       SELECT
       @@SERVERNAME,
       ''?'',
       u.name,
       CASE WHEN (r.principal_id IS NULL) THEN ''public'' ELSE
       r.name END GroupName,
       l.name LoginName,
       l.default_database_name,
       u.default_schema_name,
       u.principal_id,
       u.sid
       FROM [?].sys.database_principals u
       LEFT JOIN ([?].sys.database_role_members m
       JOIN [?].sys.database_principals r
       ON m.role_principal_id = r.principal_id)
       ON m.member_principal_id = u.principal_id
       LEFT JOIN [?].sys.server_principals l
       ON u.sid = l.sid
       WHERE u.TYPE <> ''R''
       /*and u.name like ''tester''*/
       order by u.name
       '
       END
       SELECT *
       FROM #TUser
       ORDER BY DBName,
       [name],
       GroupName
       DROP TABLE #TUser
       END
       /** end of script **/
       *****************************************************