Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Security Extract (Reverse Engineer) for ALL Databases Expand / Collapse
Author
Message
Posted Wednesday, August 3, 2011 1:10 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:14 AM
Points: 361, Visits: 1,177
Comments posted to this topic are about the item SQL Server Security Extract (Reverse Engineer) for ALL Databases

¤ §unshine ¤
Post #1153296
Posted Wednesday, August 3, 2011 4:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 3, 2014 1:04 PM
Points: 9, Visits: 454
But there's nothing to engineer with!

Where is your code?

Post #1153391
Posted Wednesday, August 3, 2011 10:14 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 3:06 PM
Points: 8, Visits: 147
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.
Post #1153704
Posted Thursday, August 4, 2011 8:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 3:26 PM
Points: 10, Visits: 1,083
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
Post #1154299
Posted Friday, September 9, 2011 7:33 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:14 AM
Points: 361, Visits: 1,177
Thank you! I apologize for any errors. I did not receive any on my end when originally creating it for one server.

¤ §unshine ¤
Post #1172517
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse