SQL Security Auditting

For allowing more general auditing between database environments you could find the following is useful for dumping logins, users, roles and their releated object permissions into a comparable format.

CREATE SCHEMA [audit] AUTHORIZATION [db_owner]

go

-- =============================================
-- Description: Audit Serverlevel logins and roles
-- =============================================
CREATE PROCEDURE [audit].Serverlevel_logins_and_roles
AS
  BEGIN
      --Server level Logins and roles
      SELECT sp.NAME                  AS LoginName,
             sp.type_desc             AS LoginType,
             sp.default_database_name AS DefaultDBName,
             slog.sysadmin            AS SysAdmin,
             slog.securityadmin       AS SecurityAdmin,
             slog.serveradmin         AS ServerAdmin,
             slog.setupadmin          AS SetupAdmin,
             slog.processadmin        AS ProcessAdmin,
             slog.diskadmin           AS DiskAdmin,
             slog.dbcreator           AS DBCreator,
             slog.bulkadmin           AS BulkAdmin
      FROM   sys.server_principals sp
             JOIN master..syslogins slog
               ON sp.sid = slog.sid
      WHERE  sp.type <> 'R'
             AND sp.NAME NOT LIKE '##%'
  END

go

-- =============================================
-- Description: Audit Databaselevel users and roles
-- =============================================
CREATE PROCEDURE [audit].Databaselevel_users_and_roles
AS
  BEGIN
      DECLARE @SQLStatement VARCHAR(4000)
      DECLARE @T_DBuser TABLE
        (
           dbname           SYSNAME,
           username         SYSNAME,
           associateddbrole NVARCHAR(256)
        )

      SET @SQLStatement=
' SELECT ''?'' AS DBName,dp.name AS UserName,USER_NAME(drm.role_principal_id) AS AssociatedDBRole  FROM ?.sys.database_principals dp LEFT OUTER JOIN ?.sys.database_role_members drm ON dp.principal_id=drm.member_principal_id  WHERE dp.sid NOT IN (0x01) AND dp.sid IS NOT NULL AND dp.type NOT IN (''C'') AND dp.is_fixed_role <> 1 AND dp.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY DBName'

    INSERT @T_DBuser
    EXEC Sp_msforeachdb
      @SQLStatement

    SELECT dbname,
           username,
           associateddbrole
    FROM   @T_DBuser
    ORDER  BY dbname,
              username,
              associateddbrole
END

go

-- =============================================
-- Description: Audit Objectlevel permissions
-- =============================================
CREATE PROCEDURE [audit].Objectlevel_permissions
AS
  BEGIN
      DECLARE @Obj VARCHAR(4000)
      DECLARE @T_Obj TABLE
        (
           dbname     SYSNAME,
           username   SYSNAME,
           issqlrole  BIT,
           objectname SYSNAME,
           permission NVARCHAR(128)
        )

      SET @Obj='USE [?]; SELECT ''?'' AS DBName, Us.name AS username, Us.issqlrole AS issqlrole, Obj.name AS object, dp.permission_name AS permission  FROM sys.database_permissions dp JOIN sys.sysusers Us  ON dp.grantee_principal_id = Us.uid  JOIN sys.sysobjects Obj ON dp.major_id = Obj.id '

      -- for each database can be done with USE [?];
      INSERT @T_Obj
      EXEC Sp_msforeachdb
        @Obj

      --SELECT UserName, issqlrole, DBName, ObjectName, Permission FROM @T_Obj ORDER BY UserName, issqlrole, DBName, ObjectName, Permission
      SELECT dbname,
             objectname,
             permission,
             username,
             issqlrole
      FROM   @T_Obj
      WHERE  dbname NOT IN ( 'master', 'msdb' )
      ORDER  BY dbname,
                objectname,
                permission,
                username,
                issqlrole
  END

go