Scripts para obtener la información de seguridad de SQL Server

Problema:

Encontrar la información de seguridad referente a los accesos (logins), usuarios y roles y permisos a nivel de objetos en las bases de datos en SQL Server.

Solución:

Script que obtiene los logins de servidor y los roles asignados a estos:

SELECTa.name AS LoginName,a.type_desc AS LoginType, a.default_database_name AS DefaultDBName,CASE
    WHEN b.sysadmin = 1 THEN 'sysadmin'
    WHEN b.securityadmin=1 THEN 'securityadmin'
    WHEN b.serveradmin=1 THEN 'serveradmin'
    WHEN b.setupadmin=1 THEN 'setupadmin'
    WHEN b.processadmin=1 THEN 'processadmin'
    WHEN b.diskadmin=1 THEN 'diskadmin'
    WHEN b.dbcreator=1 THEN 'dbcreator'
    WHEN b.bulkadmin=1 THEN 'bulkadmin'ELSE 'Public' END AS 'ServerRole'FROM sys.server_principals a  JOIN MASTER..syslogins b
    ON a.sid=b.sid WHERE a.TYPE  <> 'R'
      AND a.name NOT LIKE '##%'
GO

Script que obtiene los usuarios de base de datos y los roles asignados a estos:

DECLARE @DBuser_sql VARCHAR(4000);DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200));

SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a
LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name';

INSERT @DBuser_tableEXEC sp_MSforeachdb @command1 = @dbuser_sql
   SELECT * FROM @DBuser_table ORDER BY DBName;
GO

Script que obtiene los permisos a nivel de objeto de los usuarios de base de datos:

DECLARE @Obj_sql VARCHAR(2000);
DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), ObjectName VARCHAR(500), Permission VARCHAR(200));

SET @Obj_sql='select ''?'' as DBName,U.name as username, O.name as object,  permission_name as permission from ?.sys.database_permissions
join ?.sys.sysusers U on grantee_principal_id = uid join ?.sys.sysobjects O on major_id = id WHERE ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') order by U.name ';

INSERT @Obj_table
   EXEC sp_msforeachdb @command1 = @Obj_sql
       SELECT * FROM @Obj_table;
GO


Referencias:

SCRIPT TO RETRIEVE SECURITY INFORMATION SQL SERVER 2005 AND ABOVE