Security Change Snapshot2取得安全性權限的資訊

    參考Nate Hughes的Security Change Snapshot這篇，可以取得SQL Server Logins, Database Permissions and Role Members的安全性資訊，還可用日期取得異動的紀錄，這想法很不錯，但少了伺服器層級的安全性資訊，於是自己加了上去，並改成SP就放上來啦，很感謝Nate Hughes的分享

USE master

GO

IF OBJECT_ID ( 'dbo.uspGetSecuritySnapshot', 'P' ) IS NOT NULL

    DROP PROCEDURE dbo.uspGetSecuritySnapshot;

GO

/*********************************************************************************************

使用說明如下：(在SQL 2005,SQL 2008 R2,SQL 2012測試過)

--取得所有安全性權限的資訊

EXEC dbo.uspGetSecuritySnapshot Null,Null,Null

GO

--取得登入帳號test安全性權限的資訊

EXEC dbo.uspGetSecuritySnapshot N'test',Null,Null

GO

--取得資料庫AdventureWorks2008R2安全性權限的資訊

EXEC dbo.uspGetSecuritySnapshot Null,N'AdventureWorks2008R2',Null

GO

--取得之後異動的安全性權限

EXEC dbo.uspGetSecuritySnapshot Null,Null,'20130101'

GO

*********************************************************************************************/

CREATE  PROCEDURE dbo.uspGetSecuritySnapshot

    @Login NVARCHAR(128), --登入

    @Database NVARCHAR(128), --資料庫

    @ModDate DATETIME --日期

AS

SET NOCOUNT ON;

DECLARE  @SQL NVARCHAR(MAX);

----- SQL SERVER PERMISSIONS -----

IF @Database IS NULL

   SELECT [Database] = (CASE WHEN sp.class = 105 THEN 'master' ELSE DB_NAME(sp.major_id) END)

          , [PermissionState] = sp.State_Desc

          , [Permission] = sp.Permission_Name

          , [Login] = sp1.Name

          , LoginType = sp1.type_desc

          , [Class] = sp.class_desc

          , [Endpoint name] = e.name

          , [LastModified] = sp1.modify_date

      FROM sys.server_permissions sp

      INNER JOIN sys.server_principals sp1 ON sp.Grantee_Principal_Id = sp1.Principal_Id

      LEFT JOIN sys.endpoints e ON sp.major_id = e.endpoint_id

      WHERE sp1.is_disabled = 0

      AND   sp1.name = ISNULL(@Login, sp1.name)

      AND   sp1.modify_date >= ISNULL(@ModDate,sp1.modify_date)

      ORDER BY  sp1.Name

     

     

----- SQL SERVER LOGINS -----

   SELECT [ServerLogin] = sp.name

          , [LoginType] = sp.type_desc

          , [ServerRole] = STUFF((

                          SELECT ',' + sp2.name

                          FROM  master.sys.server_role_members rm

                                INNER JOIN master.sys.server_principals sp2 ON rm.role_principal_id = sp2.principal_id

                          WHERE sp.principal_id = rm.member_principal_id

                          FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'')

          , [DefaultDB] = sp.default_database_name

          , [LastModified] = sp.modify_date

   FROM  master.sys.server_principals sp

   WHERE sp.is_disabled = 0

   AND   sp.name = ISNULL(@Login, sp.name)

   AND   sp.modify_date >= ISNULL(@ModDate,sp.modify_date)

   AND   sp.default_database_name = ISNULL(@Database, sp.default_database_name)

   ORDER BY sp.name;

----- DATABASE PERMISSIONS -----

CREATE TABLE #DB (

   [Database] NVARCHAR(128)

   , [PermissionState] NVARCHAR(60)

   , [Permission] NVARCHAR(128)

   , [Object] NVARCHAR(128)

   , [Login] NVARCHAR(128)

   , LoginType NVARCHAR(60)

   , [LastModified] DATETIME

);

SET @SQL = 'USE ?;

INSERT INTO #DB (

   [Database]

   , [PermissionState]

   , [Permission]

   , [Object]

   , [Login]

   , LoginType

   , [LastModified]

)

SELECT [Database] = DB_NAME()

      , [PermissionState] = p.state_desc

      , [Permission] = p.permission_name

      , [Object] = CASE WHEN p.class = 0 THEN ''DB: '' + DB_NAME(p.major_id)

             WHEN p.class = 3 THEN ''Schema: '' + s.name

             ELSE ''Object: '' + OBJECT_NAME(p.major_id)

          END

      , [Login] = dp.name

      , LoginType = dp.type_desc

      , [LastModified] = dp.modify_date

FROM  sys.database_principals dp

      INNER JOIN sys.database_permissions p ON dp.principal_id = p.grantee_principal_id

      LEFT OUTER JOIN sys.objects so ON p.major_id = so.object_id AND p.class = 1

      LEFT OUTER JOIN sys.schemas s ON p.major_id = s.schema_id AND p.class = 3

WHERE dp.name = ISNULL(' + CASE WHEN @Login IS NOT NULL THEN '''' + @Login + '''' ELSE 'NULL' END + ', dp.name)

AND   dp.modify_date >= ISNULL(' + CASE WHEN @ModDate IS NOT NULL THEN '''' + CONVERT(VARCHAR,@ModDate) + '''' ELSE 'NULL' END + ',dp.modify_date);';

EXECUTE sp_MSforeachdb @SQL;

SELECT [Database]

      , [PermissionState]

      , [Permission]

      , [Object]

      , [Login]

      , LoginType

      , [LastModified]

FROM  #DB

WHERE [Database] = ISNULL(@Database, [Database])

ORDER BY [Database]

      , [Login]

      , [Object];

----- ROLE MEMBERS -----

CREATE TABLE #RM (

   [Database] NVARCHAR(128)

   , [Role] NVARCHAR(128)

   , [Login] NVARCHAR(512)

   , [LastModified] DATETIME

);

SET @SQL = 'USE ?;

SET QUOTED_IDENTIFIER ON;

INSERT INTO #RM (

   [Database]

   , [Role]

   , [Login]

   , [LastModified]

)

SELECT [Database] = DB_NAME()

      , [Role]

      , [Login]

      , [LastModified]

FROM  (

          SELECT [Role] = sp.name

                , [Login] = STUFF((

                          SELECT '','' + sp2.name

                          FROM  sys.database_role_members rm

                                INNER JOIN sys.database_principals sp2 ON rm.member_principal_id = sp2.principal_id

                          WHERE rm.role_principal_id = sp.principal_id

                          AND   sp2.name = ISNULL(' + CASE WHEN @Login IS NOT NULL THEN '''' + @Login + '''' ELSE 'NULL' END + ', sp2.name)

                          AND   sp2.modify_date >= ISNULL(' + CASE WHEN @ModDate IS NOT NULL THEN '''' + CONVERT(VARCHAR,@ModDate) + '''' ELSE 'NULL' END + ', sp2.modify_date)

                          FOR XML PATH(''''),TYPE).value(''.'',''VARCHAR(MAX)''),1,1,'''')

                , [LastModified] = sp.modify_date

          FROM  sys.database_principals sp

          WHERE sp.type IN (''R'',''A'') --DATABASE_ROLE,APPLICATION_ROLE

      ) x

WHERE x.[Login] IS NOT NULL;';

EXECUTE sp_MSforeachdb @SQL;

SELECT [Database]

   , [Role]

   , [Login]

   , [LastModified]

FROM  #RM

WHERE [Database] = ISNULL(@Database, [Database])

ORDER BY [Database]

      , [Role]

      , [Login];

DROP TABLE #DB;

DROP TABLE #RM;

GO