Following script can be used to get list of Database Roles and Members for all the databases in current SQL server instance. This information can be pulled using SQL Server Management Studio , but if you have lots of databases it might takes very long time.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
USE [master]; GO DECLARE @SQL NVARCHAR(MAX); IF OBJECT_ID('tempdb..#role_members') IS NOT NULL DROP TABLE #role_members CREATE TABLE #role_members ( [Server_Name] NVARCHAR(128), [Database_Name] NVARCHAR(128), [Member_Name] NVARCHAR(128), [Role_Name] NVARCHAR(128), [Default_Schema] NVARCHAR(128), [Server_Login] NVARCHAR(128) ) DECLARE #databases CURSOR FOR SELECT NAME FROM sys.databases WHERE state_desc = 'ONLINE' DECLARE @database_name varchar(128) OPEN #databases FETCH #databases INTO @database_name WHILE @@FETCH_STATUS = 0 BEGIN SELECT @SQL = 'INSERT INTO #role_members([Server_Name],[Database_Name],[Member_Name],[Role_Name],[Default_Schema],[Server_Login] ) ' SELECT @SQL = @SQL +' SELECT @@Servername AS [Server_Name],''' + @database_name+ ''' AS [Database_Name], m.name as [Member_Name], ' SELECT @SQL = @SQL +' r.name as [Role_Name],m.default_schema_name AS [Default_Schema] , SP.name AS [Server_Login] ' SELECT @SQL = @SQL + ' FROM ['+@database_name+'].sys.database_role_members rm ' SELECT @SQL = @SQL + ' INNER JOIN ['+@database_name+'].sys.database_principals r on rm.role_principal_id = r.principal_id' SELECT @SQL = @SQL + ' INNER JOIN ['+@database_name+'].sys.database_principals m on rm.member_principal_id = m.principal_id' SELECT @SQL = @SQL + ' INNER JOIN ['+@database_name+'].sys.server_principals AS SP ON m.[sid] = SP.[sid] ' EXEC(@SQL) FETCH #databases INTO @database_name END CLOSE #databases DEALLOCATE #databases SELECT * FROM #role_members ORDER BY Database_Name,Member_Name,Role_Name DROP TABLE #role_members |
Leave a Comment