(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 **/ *****************************************************