-------------------------------------------------------------- 2. Login Information -------------------------------------------------------------- SELECT name, create_date, modify_date, default_database_name, type_desc FROM sys.server_principals WHERE type NOT IN ('R', 'C'); -------------------------------------------------------------- 2. Login Server Roles -------------------------------------------------------------- SELECT name, CASE WHEN bulkadmin = 1 THEN 'YES' ELSE 'NO' END AS IsBulkAdmin, CASE WHEN dbcreator = 1 THEN 'YES' ELSE 'NO' END AS IsDBCreater, CASE WHEN diskadmin = 1 THEN 'YES' ELSE 'NO' END AS IsDiskAdmin, CASE WHEN Processadmin = 1 THEN 'YES' ELSE 'NO' END AS IsProcessAdmin, CASE WHEN securityadmin = 1 THEN 'YES' ELSE 'NO' END AS IsSecurityAdmin, CASE WHEN serveradmin = 1 THEN 'YES' ELSE 'NO' END AS IsServerAdmin, CASE WHEN setupadmin = 1 THEN 'YES' ELSE 'NO' END AS IsSetupAdmin, CASE WHEN sysadmin = 1 THEN 'YES' ELSE 'NO' END AS IsSysAdmin FROM sys.syslogins WHERE name NOT LIKE '#%#'; -------------------------------------------------------------- 3. Logins Information -------------------------------------------------------------- SELECT name, create_date, modify_date, default_database_name, type_desc FROM sys.server_principals WHERE type NOT IN ('R', 'C'); --Login Server Roles SELECT name, CASE WHEN bulkadmin = 1 THEN 'YES' ELSE 'NO' END AS IsBulkAdmin, CASE WHEN dbcreator = 1 THEN 'YES' ELSE 'NO' END AS IsDBCreater, CASE WHEN diskadmin = 1 THEN 'YES' ELSE 'NO' END AS IsDiskAdmin, CASE WHEN Processadmin = 1 THEN 'YES' ELSE 'NO' END AS IsProcessAdmin, CASE WHEN securityadmin = 1 THEN 'YES' ELSE 'NO' END AS IsSecurityAdmin, CASE WHEN serveradmin = 1 THEN 'YES' ELSE 'NO' END AS IsServerAdmin, CASE WHEN setupadmin = 1 THEN 'YES' ELSE 'NO' END AS IsSetupAdmin, CASE WHEN sysadmin = 1 THEN 'YES' ELSE 'NO' END AS IsSysAdmin FROM sys.syslogins WHERE name NOT LIKE '#%#'; -------------------------------------------------------------- 4. Logins Database mappings -------------------------------------------------------------- IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'LoginDBRoleMemebers' AND type = 'U') BEGIN TRUNCATE TABLE LoginDBRoleMemebers; END ELSE BEGIN CREATE TABLE LoginDBRoleMemebers ( DbRole sysname , MemberName sysname , MemberSID VARBINARY (85), DBName VARCHAR (128) ); END IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'tmp_LoginDBRoleMemebers' AND type = 'U') BEGIN TRUNCATE TABLE tmp_LoginDBRoleMemebers; END ELSE BEGIN CREATE TABLE tmp_LoginDBRoleMemebers ( DbRole sysname , MemberName sysname , MemberSID VARBINARY (85) ); END DECLARE @dbname AS VARCHAR (128), @sql AS VARCHAR (500); DECLARE LoginMapping_cursor CURSOR FOR --get all user databases SELECT name FROM sys.databases WHERE database_id > 4; OPEN LoginMapping_cursor; FETCH NEXT FROM LoginMapping_cursor INTO @dbname; WHILE @@fetch_status = 0 BEGIN SELECT @sql = 'use ' + @dbname + ' ' + 'exec sp_helprolemember'; INSERT INTO tmp_LoginDBRoleMemebers EXECUTE (@sql); INSERT INTO LoginDBRoleMemebers (DbRole, MemberName, MemberSID, DBName) SELECT DbRole, MemberName, MemberSID, @dbname FROM tmp_LoginDBRoleMemebers; TRUNCATE TABLE tmp_LoginDBRoleMemebers; FETCH NEXT FROM LoginMapping_cursor INTO @dbname; END CLOSE LoginMapping_cursor; DEALLOCATE LoginMapping_cursor; SELECT MemberName AS LoginName, DBName, DBRole FROM LoginDBRoleMemebers WHERE MemberName <> 'dbo' ORDER BY MemberName; DROP TABLE LoginDBRoleMemebers; DROP TABLE tmp_LoginDBRoleMemebers;