SQL Server Security Extract (Reverse Engineer) for ALL Databases

  • Comments posted to this topic are about the item SQL Server Security Extract (Reverse Engineer) for ALL Databases

    ¤ §unshine ¤

  • But there's nothing to engineer with!

    Where is your code?

  • I don't know much that much about encoding, but it seems that your code had some unprintable characters in it that prevents a simple copy and paste into sql server. Viewing the source of the page, it seems the encoding is UTF-8, which didn't like your tabs.

    Msg 102, Level 15, State 1, Line 68

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 70

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 71

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 102

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 103

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 10

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 12

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 13

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 44

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 45

    Incorrect syntax near '?'.

    Once fixed, would look like:

    -- =============================================

    -- Author: Shirley Noa

    -- Create date: 2011-08-03

    -- Description: These scripts will extract security for Windows Logins, Database Users, Roles, Objects and SQL Logins.

    -- The only caveat is that the sql logins do not script it's database user or roles tied to it.This was taken from several

    -- sources online and changed to fit what I needed.

    -- You can run this as results to text and save, or results to file.

    -- I used this as backup scripts before making security changes.

    -- =============================================

    -- Create SQL Logins

    SET NOCOUNT ON

    SELECT 'EXEC sp_addlogin @loginame = ''' + name + ''''

    ,', @defdb = ''' + default_database_name + ''''

    ,', @deflanguage = ''' + default_language_name + ''''

    ,', @encryptopt = ''skip_encryption'''

    ,', @passwd ='

    , cast(password_hash AS varbinary(256))

    ,', @sid ='

    , sid

    FROM sys.sql_logins

    WHERE name NOT IN ('sa')

    ORDER BY name

    -- Create Windows Logins

    EXEC sp_MSforeachdb 'Print''-- Users for [?]''

    Use [?];

    Print''Use [?];''

    select ''CREATE LOGIN ['' + server_login_name + ''] FROM WINDOWS WITH DEFAULT_DATABASE=[TEMPDB], DEFAULT_LANGUAGE=[us_english]''

    from (

    select dp.name as database_principal_name, sl.name server_login_name

    from sys.database_principals dp

    left outer join sys.server_principals sl on dp.sid = sl.sid

    where dp.type =''U''and dp.name not in (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'')

    and sl.name <> ''''

    ) ss'

    -- Create Database Users

    EXEC sp_MSforeachdb 'Print''-- Users for [?]''

    Use [?];

    Print''Use [?];''

    select ''CREATE USER ['' + database_principal_name + ''] FOR LOGIN ['' + server_login_name + ''] WITH DEFAULT_SCHEMA=[dbo]''

    from (

    select dp.name as database_principal_name, sl.name server_login_name

    from sys.database_principals dp

    left outer join sys.server_principals sl on dp.sid = sl.sid

    where dp.type NOT IN (''R'', ''G'', ''C'')and dp.name not in (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'')

    and sl.name <> ''''

    ) ss'

    -- ADD ROLE MEMBERS

    EXEC sp_MSforeachdb 'Print''-- Role Members for [?]''

    Print''Use [?];''

    Use [?];SELECT ''EXEC sp_addrolemember '''''' + rp.name + '''''', '''''' + mp.name + '''''''' AS [-- CreateRoleSQL]

    FROM sys.database_role_members a

    INNER JOIN sys.database_principals rp ON rp.principal_id = a.role_principal_id

    INNER JOIN sys.database_principals AS mp ON mp.principal_id = a.member_principal_id

    '

    -- Remove ROLE MEMBERS

    EXEC sp_MSforeachdb 'Print''-- Role Members for [?]''

    Print''Use [?];''

    Use [?];SELECT

    ''EXEC sp_droprolemember '''''' + rp.name + '''''', '''''' + mp.name + '''''''' AS [-- RemoveRoleSQL]

    FROM sys.database_role_members a

    INNER JOIN sys.database_principals rp ON rp.principal_id = a.role_principal_id

    INNER JOIN sys.database_principals AS mp ON mp.principal_id = a.member_principal_id

    '

    -- CREATE GRANT Object PERMISSIONS SCRIPT

    Declare @sqlstm nvarchar(max)

    Declare @dbname sysname

    Declare rolldbname cursor for

    Select name from master.dbo.sysdatabases where dbid>=5

    open rolldbname

    fetch next from rolldbname into @dbname

    While @@fetch_status=0

    BEGIN

    PRINT '-- Permissions for ' + @dbname

    SET @sqlstm=

    'Print''-- Role Members for [?]''

    Print''Use ['+@DBName+ '];''

    Use ['+@DBName+ '] SELECT

    StateDesc + '' '' + PermissionName + '' ON SCHEMA::['' + s.name + ''] TO ['' + p.name + ''];'' AS [--RemoveSQL]

    FROM sys.database_permissions d

    INNER JOIN sys.database_principals p ON d.grantee_principal_id = p.principal_id

    INNER JOIN sys.schemas AS s ON s.schema_id = d.major_id

    CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,

    permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionName) ca

    WHERE d.class = 3 /*''SCHEMA''*/

    UNION ALL

    SELECT

    StateDesc + '' '' + PermissionName + '' TO ['' + p.name + ''];'' AS RemoveSQL

    FROM sys.database_permissions d

    INNER JOIN sys.database_principals p ON d.grantee_principal_id = p.principal_id

    CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,

    permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionName) ca

    WHERE d.class = 0 /*DATABASE*/

    UNION ALL

    SELECT

    StateDesc + '' '' + PermissionName + '' ON ['' + s.name + ''].['' + o.name + ''] '' + ISNULL(''(['' + co.NAME + '']) '', '' '') + ''TO ['' + p.name + ''];'' AS RemoveSQL

    FROM sys.database_permissions d

    INNER JOIN sys.database_principals p ON d.grantee_principal_id = p.principal_id

    INNER JOIN sys.objects AS o ON o.object_id = d.major_id

    INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id

    LEFT OUTER JOIN sys.columns co ON co.object_id = o.object_id

    AND co.column_id = d.minor_id

    CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,

    permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionName) ca

    WHERE d.class = 1 /*OBJECT OR COLUMN*/'

    EXEC (@sqlstm)

    fetch next from rolldbname into @dbname

    END

    close rolldbname

    deallocate rolldbname

    go

    -- CREATE REMOVE Object PERMISSIONS SCRIPT

    Declare @sqlstm nvarchar(max)

    Declare @dbname sysname

    Declare rolldbname cursor for

    Select name from master.dbo.sysdatabases where dbid>=5

    open rolldbname

    fetch next from rolldbname into @dbname

    While @@fetch_status=0

    BEGIN

    PRINT '-- Permissions for ' + @dbname

    SET @sqlstm=

    'Print''-- Role Members for [?]''

    Print''Use ['+@DBName+ '];''

    Use ['+@DBName+ '] SELECT

    ''REVOKE '' ++ '' '' + PermissionName + '' ON SCHEMA::['' + s.name + ''] TO ['' + p.name + ''];'' AS [--RemoveSQL]

    FROM sys.database_permissions d

    INNER JOIN sys.database_principals p ON d.grantee_principal_id = p.principal_id

    INNER JOIN sys.schemas AS s ON s.schema_id = d.major_id

    CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,

    permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionName) ca

    WHERE d.class = 3 /*''SCHEMA''*/

    UNION ALL

    SELECT

    ''REVOKE '' ++ '' '' + PermissionName + '' TO ['' + p.name + ''];'' AS RemoveSQL

    FROM sys.database_permissions d

    INNER JOIN sys.database_principals p ON d.grantee_principal_id = p.principal_id

    CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,

    permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionName) ca

    WHERE d.class = 0 /*DATABASE*/

    UNION ALL

    SELECT

    ''REVOKE '' ++ '' '' + PermissionName + '' ON ['' + s.name + ''].['' + o.name + ''] '' + ISNULL(''(['' + co.NAME + '']) '', '' '') + ''TO ['' + p.name + ''];'' AS RemoveSQL

    FROM sys.database_permissions d

    INNER JOIN sys.database_principals p ON d.grantee_principal_id = p.principal_id

    INNER JOIN sys.objects AS o ON o.object_id = d.major_id

    INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id

    LEFT OUTER JOIN sys.columns co ON co.object_id = o.object_id

    AND co.column_id = d.minor_id

    CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,

    permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionName) ca

    WHERE d.class = 1 /*OBJECT OR COLUMN*/'

    EXEC (@sqlstm)

    fetch next from rolldbname into @dbname

    END

    close rolldbname

    deallocate rolldbname

    go

    After that, the only errors left for me are from the --Create Database Users script,

    which results in

    Msg 451, Level 16, State 1, Line 4

    Cannot resolve collation conflict for column 1 in SELECT statement.

    Msg 451, Level 16, State 1, Line 4

    Cannot resolve collation conflict for column 1 in SELECT statement.

    when it runs against my report server and report server tempdb databases.

  • change the sub-select collation:

    ...from...

    from (

    select dp.name as database_principal_name, sl.name server_login_name

    ...to...

    from (

    select dp.name COLLATE SQL_Latin1_General_CP1_CI_AS as database_principal_name, sl.name server_login_name

  • Thank you! I apologize for any errors. I did not receive any on my end when originally creating it for one server.

    ¤ §unshine ¤

  • Thanks for the script and the updates.

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

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