In this post I wanted to script out the following in a sql server instance:
Database Name, Login Type, Login Name, Server Role, Database User, and Database Role. It is always good to have them stored in the database server documentation. Below is the script:
CREATE TABLE [dbo].[#Temp] ( [DATABASE NAME] NVARCHAR(128) NULL, [LOGIN TYPE] VARCHAR(30) NULL, [ServerLogin] CHAR(45) NULL, [ServerRole] CHAR(45) NULL, [Database User] CHAR(25) NULL, [Database Role] CHAR(25) NULL) EXEC sp_msForEachDB ' INSERT INTO #Temp select ''?'' As ''Database Name'', [Login Type]= case sp.type when ''u'' then ''WindowsLogin'' when ''s'' then ''SQLLogin'' when ''g'' then ''WindowsGroup'' end, convert(char(45),sp.name) as srvLogin, convert(char(45),sp2.name) as srvRole, convert(char(25),dbp.name) as dbUser, convert(char(25),dbp2.name) as dbRole from sys.server_principals as sp join [?].sys.database_principals as dbp on sp.sid=dbp.sid join [?].sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id join [?].sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id ' SELECT * FROM #Temp
Hope this helps!