SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Security Extract (Reverse Engineer) for ALL Databases


SQL Server Security Extract (Reverse Engineer) for ALL Databases

Author
Message
sunshine-587009
sunshine-587009
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2195 Visits: 1280
Comments posted to this topic are about the item SQL Server Security Extract (Reverse Engineer) for ALL Databases

¤ §unshine ¤
paultormey
paultormey
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 651
But there's nothing to engineer with!

Where is your code?
fjzimmermanv
fjzimmermanv
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 161
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.
Ricky Lively
Ricky Lively
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 1765
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
sunshine-587009
sunshine-587009
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2195 Visits: 1280
Thank you! I apologize for any errors. I did not receive any on my end when originally creating it for one server.

¤ §unshine ¤
Iwas Bornready
Iwas Bornready
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21916 Visits: 885
Thanks for the script and the updates.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search