Security Change Snapshot

  • Comments posted to this topic are about the item Security Change Snapshot

    _____________________________________________________________________
    - Nate

    @nate_hughes
  • I found bug with complexe database name, example : SharePoint_AdminContent_3a1c2e47-b402-4304-b38d-c036ebea9f6b

  • using QUOTENAME() around the database name should correct your issue.

  • Doh! Brackets.

    I've also added an orphaned users section at the end.

    SET NOCOUNT ON;

    DECLARE @login NVARCHAR(128)

    , @ModDate DATETIME

    , @Database NVARCHAR(128)

    , @SQL NVARCHAR(MAX);

    --SET @login = 'applogin';

    --SET @ModDate = '12/12/2012';

    SET @Database = 'SharePoint_AdminContent_3a1c2e47-b402-4304-b38d-c036ebea9f6b';

    ----- SERVER LOGINS -----

    IF @Database IS NULL

    SELECT[ServerLogin] = sp.name

    , [LoginType] = sp.type_desc

    , [ServerRole] = STUFF((

    SELECT',' + sp2.name

    FROMmaster.sys.server_role_members rm

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

    WHEREsp.principal_id = rm.member_principal_id

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

    , [DefaultDB] = sp.default_database_name

    , [LastModified] = sp.modify_date

    FROMmaster.sys.server_principals sp

    WHEREsp.is_disabled = 0

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

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

    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

    FROMsys.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

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

    ANDdp.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

    FROMsys.database_role_members rm

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

    WHERErm.role_principal_id = sp.principal_id

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

    ANDsp2.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

    FROMsys.database_principals sp

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

    ) x

    WHEREx.[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];

    ----- ORPHAN USERS -----

    CREATE TABLE #OU (

    [Database] NVARCHAR(128)

    , [Login] NVARCHAR(128)

    , [LastModified] DATETIME

    );

    SET @SQL = 'USE [?];

    SET QUOTED_IDENTIFIER ON;

    INSERT INTO #OU (

    [Database]

    , [Login]

    , [LastModified]

    )

    SELECT[Database] = DB_NAME()

    , d.name

    , d.modify_date

    FROMsys.database_principals d

    LEFT OUTER JOIN sys.server_principals s

    ON d.sid = s.sid

    WHEREs.sid IS NULL

    ANDd.type IN (''U'', ''S'') -- WINDOWS_USER, SQL_USER

    ANDd.name NOT IN (''guest'', ''INFORMATION_SCHEMA'', ''sys'');'

    EXECUTE sp_MSforeachdb @SQL;

    SELECT[Database]

    , [Login]

    , [LastModified]

    FROM#OU

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

    ORDER BY [Database]

    , [Login];

    DROP TABLE #DB;

    DROP TABLE #RM;

    DROP TABLE #OU;

    _____________________________________________________________________
    - Nate

    @nate_hughes
  • two isues:

    one: already metioned. spaces and dashes in the database names:

    USE [' + '?'+ '];

    OR just USE [?]; as stated.

    two: temprary tables drop only if the script runs successfully. When the script fails you get stuck with the one of the temp tables already being there. It is a better idea to drop temp tables just before creating them:

    IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[#DB]') AND type in (N'U'))

    DROP TABLE [tempdb].[dbo].[#DB];

    Alex Donskoy

    SQL DBA Greenberg & Trauriq PA

    MIAMI, FL

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply