Cela faisait un moment que je n’ai pas posté de billets !! Je recommence doucement en vous proposant une procédure qui permet d’auditer la sécurité (comptes de connexion et utilisateurs) d’une instance SQL Server. Cette dernière est valable pour les version 2000, 2005, 2008 et 2008 R2.

/***********************************
* @Author = BARBARIN DAVID        *
* @Date = 05/20/2011              *
* @Description =                  *
* Extract account privileges          *
* on SQL Server 2000, 2005  and 2008   *
***********************************/ 

– Drop temp tables if exists
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(‘tempdb.dbo.#tlogin’))
  DROP TABLE #tlogin
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(‘tempdb.dbo.#trole’))
  DROP TABLE #trole
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(‘tempdb.dbo.#tuser’))
  DROP TABLE #tuser
CREATE TABLE #trole
(
DBName VARCHAR(50),
RoleName SYSNAME NULL,
UserName SYSNAME NULL,
ObjectType VARCHAR(100),
ObjectName SYSNAME NULL,
Permission SYSNAME NULL
)

– Create temp table for users
CREATE TABLE #tuser
(
DBName VARCHAR(50),
UserName SYSNAME,
ObjectType VARCHAR(100),
ObjectName SYSNAME NULL,
Permission SYSNAME NULL
)

– Create temp table for logins
CREATE TABLE #tlogin
(
LoginName SYSNAME,
SrvRoleName SYSNAME
)

IF (LEFT(CAST(SERVERPROPERTY(‘ProductVersion’) AS VARCHAR(50)),1) = ‘9’
OR LEFT(CAST(SERVERPROPERTY(‘ProductVersion’) AS VARCHAR(50)),2) = ’10’)

BEGIN
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(‘tempdb.dbo.#tloginpermissions’))
  DROP TABLE #tloginpermissions
  — Create temp table  for logins permissions
CREATE TABLE #tloginpermissions
(
  LoginName SYSNAME,
  LoginTypeDesc SYSNAME,
  LoginPermission SYSNAME,
  LoginPermissionState SYSNAME
)
END

– Create temp tables for roles and user members
/* Extract Server Login  */
– SQL Server 2000
IF LEFT(CAST(SERVERPROPERTY(‘ProductVersion’) AS VARCHAR(50)),1) = ‘8’
BEGIN
INSERT #tlogin
SELECT
  loginname AS login_name,
  ‘sysadmin’ AS role_name
FROM master..syslogins
WHERE sysadmin = 1
UNION ALL
SELECT
  loginname AS login_name,
  ‘securityadmin’
FROM master..syslogins
WHERE securityadmin = 1
UNION ALL
SELECT
  loginname AS login_name,
  ‘serveradmin’
FROM master..syslogins
WHERE serveradmin = 1
UNION ALL
SELECT
  loginname AS login_name,
  ‘setupadmin’
FROM master..syslogins
WHERE setupadmin = 1
UNION ALL
SELECT
  loginname AS login_name,
  ‘processadmin’
FROM master..syslogins
WHERE processadmin = 1
UNION ALL
SELECT
  loginname AS login_name,
  ‘diskadmin’
FROM master..syslogins
WHERE diskadmin = 1
UNION ALL
SELECT
  loginname AS login_name,
  ‘dbcreator’
FROM master..syslogins
WHERE dbcreator = 1
UNION ALL
SELECT
  loginname AS login_name,
  ‘bulkadmin’
FROM master..syslogins
WHERE bulkadmin = 1
ORDER BY login_name, role_name
END
– SQL Server 2005 or more
ELSE IF (LEFT(CAST(SERVERPROPERTY(‘ProductVersion’) AS VARCHAR(50)),1) = ‘9’
         OR LEFT(CAST(SERVERPROPERTY(‘ProductVersion’) AS VARCHAR(50)),2) = ’10’)
BEGIN
  INSERT #tlogin
  SELECT
   sp.name AS login_name,
   sp2.name AS role_name
  FROM sys.server_principals AS sp
  LEFT JOIN sys.server_role_members srm
   ON sp.principal_id = srm.member_principal_id
  LEFT JOIN sys.server_principals AS sp2
   ON sp2.principal_id = srm.role_principal_id
  WHERE sp.type IN (‘S’, ‘U’, ‘G’)
   AND sp2.name IS NOT NULL
  INSERT #tloginpermissions
  SELECT
   sp.name AS login_name,
   sp.type_desc AS [login_type],
   spm.permission_name,
   spm.state_desc
  FROM sys.server_principals AS sp
  INNER JOIN sys.server_permissions AS spm
   ON sp.principal_id = spm.grantee_principal_id
  WHERE sp.type IN (‘S’, ‘U’, ‘G’)
  ORDER BY sp.name, spm.permission_name
END

/* Extract Database Users Privileges */
– SQL Server 2000
IF LEFT(CAST(SERVERPROPERTY(‘ProductVersion’) AS VARCHAR(50)),1) = ‘8’
BEGIN
    INSERT #tuser
    EXEC sp_msforeachdb
    ‘USE ?
     SELECT
      »? » AS database_name,
     u.name AS user_name,
     CASE o.xtype
      WHEN  »C » THEN  »CHECK constraint »
      WHEN  »D » THEN  »Default or DEFAULT constraint »
      WHEN  »F » THEN  »FOREIGN KEY constraint »
      WHEN  »L » THEN  »Log »
      WHEN  »FN » THEN  »Scalar function »
      WHEN  »IF » THEN  »Inlined table-function »
      WHEN  »P » THEN  »Stored procedure »
      WHEN  »PK » THEN  »PRIMARY KEY constraint (type is K) »
      WHEN  »RF » THEN  »Replication filter stored procedure »
      WHEN  »S » THEN  »System table »
      WHEN  »TF » THEN  »Table function »
      WHEN  »TR » THEN  »Trigger »
      WHEN  »U » THEN  »User table »
      WHEN  »UQ » THEN  »UNIQUE constraint (type is K) »
      WHEN  »V » THEN  »View »
      WHEN  »X » THEN  »Extended stored procedure »
     END AS ObjectType,
     o.name AS object_name,
     CASE pr.action
      WHEN 26 THEN  »REFERENCES »
      WHEN 193 THEN  »SELECT »
      WHEN 195 THEN  »INSERT »
      WHEN 196 THEN  »DELETE »
      WHEN 197 THEN  »UPDATE »
      WHEN 224 THEN  »EXECUTE »
     ELSE  »OTHERS »
    END AS permission_name
    FROM sysusers u
    INNER JOIN syspermissions p
     ON u.uid = p.grantee
    INNER JOIN sysobjects o
     ON p.id = o.id
    INNER JOIN sysprotects pr
     ON o.id = pr.id
      AND u.uid = pr.uid
    ORDER BY u.name, o.name’
END
– SQL Server 2005 or more
ELSE IF (LEFT(CAST(SERVERPROPERTY(‘ProductVersion’) AS VARCHAR(50)),1) = ‘9’
        OR LEFT(CAST(SERVERPROPERTY(‘ProductVersion’) AS VARCHAR(50)),2) = ’10’)
BEGIN
     PRINT ‘SQL SERVER 2008′
     INSERT #tuser
     EXEC sp_MSforeachdb ‘
     USE ?
     SELECT
       »? » AS database_name,
      p.name AS [user_name],
      pm.class_desc AS ObjectType,
      CASE pm.class
       WHEN 1 THEN o.name
       WHEN 3 THEN s.name
       WHEN 0 THEN DB_NAME()
      END object_name,
      pm.permission_name
      FROM sys.database_principals AS p
      INNER JOIN sys.database_permissions AS pm
       ON pm.grantee_principal_id = p.principal_id
      LEFT JOIN sys.objects AS o
       ON pm.major_id = o.object_id
      LEFT JOIN sys.schemas AS s
       ON pm.major_id = s.schema_id
      ORDER BY p.name, pm.class’
END

/* Extract role rights on databases */
– SQL Server 2000
IF LEFT(CAST(SERVERPROPERTY(‘ProductVersion’) AS VARCHAR(50)),1) = ‘8’
BEGIN
PRINT ‘SQL SERVER 2000′
INSERT #trole
EXEC sp_MSforeachdb ‘
USE ?
SELECT
      »? » AS database_name,
     u.name AS role_name,
     u2.name AS user_name,
     CASE o.xtype
      WHEN  »C » THEN  »CHECK constraint »
      WHEN  »D » THEN  »Default or DEFAULT constraint »
      WHEN  »F » THEN  »FOREIGN KEY constraint »
      WHEN  »L » THEN  »Log »
      WHEN  »FN » THEN  »Scalar function »
      WHEN  »IF » THEN  »Inlined table-function »
      WHEN  »P » THEN  »Stored procedure »
      WHEN  »PK » THEN  »PRIMARY KEY constraint (type is K) »
      WHEN  »RF » THEN  »Replication filter stored procedure »
      WHEN  »S » THEN  »System table »
      WHEN  »TF » THEN  »Table function »
      WHEN  »TR » THEN  »Trigger »
      WHEN  »U » THEN  »User table »
      WHEN  »UQ » THEN  »UNIQUE constraint (type is K) »
      WHEN  »V » THEN  »View »
      WHEN  »X » THEN  »Extended stored procedure »
     END AS ObjectType,
     o.name AS object_name,
     CASE pr.action
      WHEN 26 THEN  »REFERENCES »
      WHEN 193 THEN  »SELECT »
      WHEN 195 THEN  »INSERT »
      WHEN 196 THEN  »DELETE »
      WHEN 197 THEN  »UPDATE »
      WHEN 224 THEN  »EXECUTE »
     ELSE  »OTHERS »
    END AS permission_name
    FROM sysusers u
    LEFT JOIN syspermissions p
     ON u.uid = p.grantee
    LEFT JOIN sysobjects o
     ON p.id = o.id
    LEFT JOIN sysprotects pr
     ON o.id = pr.id
      AND u.uid = pr.uid
    LEFT JOIN sysmembers m
     ON u.uid = m.groupuid
    LEFT JOIN  sysusers u2
     ON m.memberuid = u2.uid
    WHERE u.issqlrole = 1
    ORDER BY u.name, u2.name, o.name’
END
– SQL Server 2005 or more
ELSE IF (LEFT(CAST(SERVERPROPERTY(‘ProductVersion’) AS VARCHAR(50)),1) = ‘9’
         OR LEFT(CAST(SERVERPROPERTY(‘ProductVersion’) AS VARCHAR(50)),2) = ’10’)
BEGIN
  PRINT ‘SQL SERVER 2008′
  INSERT #trole
      EXEC sp_MSforeachdb ‘
      USE ?
      SELECT
        »? » as database_name,
       dp.name AS role_name,
       dp2.name AS user_name,
       pm.class_desc AS ObjectType,
       CASE pm.class
        WHEN 1 THEN o.name
        WHEN 3 THEN s.name
        WHEN 0 THEN DB_NAME()
       END object_name,
       pm.permission_name
      FROM sys.database_principals AS dp
      LEFT JOIN sys.database_role_members AS rlm
       ON rlm.role_principal_id = dp.principal_id
      LEFT JOIN sys.database_principals AS dp2
       ON dp2.principal_id = rlm.member_principal_id
      LEFT JOIN sys.database_permissions AS pm
       ON dp.principal_id = pm.grantee_principal_id
      LEFT JOIN sysobjects AS o
       ON pm.major_id = o.id
     LEFT JOIN sys.schemas AS s
      ON pm.major_id = s.schema_id
     WHERE dp.type =  »R »’
END

/* Show permissions */
SELECT @@VERSION

– Logins and server roles
SELECT ‘Login and server roles’
SELECT *
FROM #tlogin

– Server permissions was introduced with SQL Server 2005
IF (LEFT(CAST(SERVERPROPERTY(‘ProductVersion’) AS VARCHAR(50)),1) = ‘9’
    OR LEFT(CAST(SERVERPROPERTY(‘ProductVersion’) AS VARCHAR(50)),2) = ’10’)
BEGIN
SELECT ‘Server login permissions’
SELECT *
FROM #tloginpermissions
END

– Role permissions
SELECT ‘Role permissions with users’
SELECT *
FROM #trole

– User permissions
SELECT ‘User permissions’
SELECT * FROM #tuser

 

A noter que je n’ai pas réalisé de concaténation dans le cas où un login appartient à plusieurs rôles de serveur par exemple. Le but de cette extraction est de pouvoir facilement l?importer dans un fichier Excel.

Bon audit !!

David BARBARIN (Mikedavem)
MVP SQL Server