This article is nice ( we have a little bug ) , and the SQL script is not execute-safe (if you run twice, you have errors ).
It is for a specific database, when you need to save all SQL Server, you must run for each database.
1
2PRINT '-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --'
3PRINT '-- SQL Security script '
4PRINT '-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --'
5PRINT '--'
6PRINT '-- Server [' + @@servername+ ']'
7PRINT '-- database [' + db_name()+ ']'
8PRINT '--'
9PRINT '-- * Add Database user'
10PRINT '-- * Add Roles'
11PRINT '-- * Set Object Specific Permissions'
12PRINT '-- * DO NOT delete anything'
13PRINT '--'
14PRINT '-- Created At: ' + CONVERT(VARCHAR, GETDATE(), 102) +' ' + CONVERT(VARCHAR, GETDATE(), 108)
15PRINT '-- Created By: ' + SUSER_NAME()
16PRINT '-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --'
17PRINT ''
18PRINT 'USE [' + DB_NAME() + ']'
19PRINT 'GO'
20PRINT 'PRINT @@Servername;'
21PRINT ''
22PRINT 'GO'
23/*
24Ref.:
25https://msdn.microsoft.com/en-US/library/ms191465.aspx
26https://msdn.microsoft.com/en-us/library/ms177596.aspx
27http://stackoverflow.com/questions/917431/sql-server-return-schema-for-sysobjects
28http://www.sql-server-performance.com/2002/object-permission-scripts/
29http://stackoverflow.com/questions/1987190/scripting-sql-server-permissions
30*/
31
32PRINT 'PRINT ''-- - - - - - - - - - - - - - - - - - - - - - - -'''
33PRINT 'PRINT ''-- Add database user'''
34SET NOCOUNT ON;
35-- SELECT * FROM sysusers
36DECLARE @DbUser TABLE (RowNumber int, DatabaseUserID [smallint], DatabaseUserName [sysname], ServerUserName [sysname])
37
38INSERT INTO @DbUser (RowNumber, DatabaseUserID, DatabaseUserName, ServerUserName)
39SELECT
40 ROW_NUMBER() OVER (ORDER BY master.dbo.syslogins.loginname) AS RowNumber
41 , sysusers.uid AS DatabaseUserID
42 , sysusers.Name AS DatabaseUserName
43 , master.dbo.syslogins.loginname AS ServerUserName
44-- SELECT *
45FROM
46 sysusers INNER JOIN master.dbo.syslogins
47 ON sysusers.sid = master.dbo.syslogins.sid
48
49DECLARE @DatabaseUserID [smallint];
50DECLARE @ServerUserName [sysname];
51DECLARE @DatabaseUserName [sysname];
52
53DECLARE @nDbUser int;
54DECLARE @i int;
55SELECT @nDbUser = COUNT(*) FROM @DbUser
56-- select * FROM @DbUser
57SET @i=1;
58WHILE (@i<@nDbUser)
59BEGIN
60 SELECT @DatabaseUserID = DatabaseUserID
61 , @ServerUserName = ServerUserName
62 , @DatabaseUserName = DatabaseUserName
63 , @i = @i + 1
64 FROM @DbUser WHERE RowNumber = @i;
65 PRINT 'IF (NOT EXISTS (SELECT sysusers.name FROM sysusers WHERE (sysusers.name = '''+@DatabaseUserName+''')))'
66 PRINT 'BEGIN'
67 PRINT ' PRINT ''-– Add User To Database : ' + CAST(@ServerUserName as NVARCHAR(MAX)) + '''';
68 PRINT ' EXEC [sp_grantdbaccess] @loginame = ''' + @ServerUserName + ''', @name_in_db = ''' + @DatabaseUserName + ''''
69 PRINT 'END'
70 PRINT 'ELSE'
71 PRINT 'BEGIN'
72 PRINT ' PRINT ''-- Database User ['+@ServerUserName+'] exists in database ['+DB_name()+'] '''
73 PRINT 'END'
74 PRINT 'GO'
75 PRINT ''
76
77END
78GO
79
80--------------------------------------------------------
81SET NOCOUNT ON;
82DECLARE @i int;
83DECLARE @RoleName [sysname];
84DECLARE @nDbUserRole int;
85DECLARE @DatabaseUserName [sysname];
86DECLARE @DbUserRole TABLE (RowNumber int, DatabaseUserName [sysname], RoleName [sysname])
87INSERT INTO @DbUserRole (RowNumber, DatabaseUserName, RoleName )
88SELECT
89 ROW_NUMBER() OVER (ORDER BY sys.sysusers.name) AS RowNumber
90 , sys.sysusers.name AS DatabaseName
91 , Roles.name AS RoleName
92FROM sys.sysusers INNER JOIN
93 master.dbo.syslogins ON sys.sysusers.sid = master.dbo.syslogins.sid INNER JOIN
94 sys.sysmembers ON sys.sysusers.uid = sys.sysmembers.memberuid INNER JOIN
95 sys.sysusers AS Roles ON sys.sysmembers.groupuid = Roles.uid
96
97SET @nDbUserRole = 1;
98SELECT @nDbUserRole = COUNT(*) FROM @DbUserRole
99SET @i = 1;
100PRINT 'PRINT ''-- - - - - - - - - - - - - - - - - - - - - - - -'''
101PRINT 'PRINT ''-- Add roles'''
102WHILE (@i<@nDbUserRole)
103BEGIN
104 SELECT @RoleName = RoleName
105 , @DatabaseUserName = DatabaseUserName
106 , @i = @i + 1
107 FROM @DbUserRole WHERE RowNumber = @i;
108 PRINT 'EXEC [sp_addrolemember] @rolename = '''+@RoleName+''''+SUBSTRING(' ',0,15-LEN(@RoleName))+', @membername = '''+@DatabaseUserName+''';';
109END
110
111GO
112
113GO
114
115GO
116--------------------------------------------------------
117SET NOCOUNT ON;
118-- https://msdn.microsoft.com/en-us/library/ms178564.aspx
119DECLARE @nDbUserPermission int;
120DECLARE @ObjectnName NVARCHAR(250);
121DECLARE @ActionStatement NVARCHAR(250);
122DECLARE @ProtectTypeStatement NVARCHAR(250);
123DECLARE @DatabaseUserName [sysname];
124DECLARE @SqlCode NVARCHAR(MAX);
125
126DECLARE @i INT;
127DECLARE @max1 INT;
128DECLARE @max2 INT;
129
130SELECT @max1 = MAX(LEN(sys.database_permissions.permission_name collate latin1_general_cs_as)) FROM sys.database_permissions
131SELECT @max2 = MAX(LEN('['+sys.schemas.name + '].['+sys.objects.name+']')) FROM sys.database_permissions INNER JOIN
132 sys.objects ON sys.database_permissions.major_id = sys.objects.object_id INNER JOIN
133 sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id INNER JOIN
134 sys.database_principals ON sys.database_permissions.grantee_principal_id = sys.database_principals.principal_id
135WHERE sys.database_principals.name NOT IN ('public', 'guest');
136
137DECLARE @DbUserPermission TABLE (RowNumber int, SqlCode nvarchar(max))
138INSERT INTO @DbUserPermission (RowNumber, SqlCode)
139SELECT
140 ROW_NUMBER() OVER (ORDER BY sys.database_permissions.state_desc) as RowNumber,
141 CASE sys.database_permissions.state_desc
142 WHEN 'GRANT' THEN 'GRANT '
143 WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT '
144 WHEN 'DENY' THEN 'DENY '
145 ELSE '*' END
146 + sys.database_permissions.permission_name collate latin1_general_cs_as + REPLICATE(' ', @max1 - LEN(CAST(sys.database_permissions.permission_name collate latin1_general_cs_as as varchar(200))))
147 + ' ON '
148 + '['+sys.schemas.name + '].['+sys.objects.name+']' + REPLICATE(' ', @max2 - LEN(CAST('['+sys.schemas.name + '].['+sys.objects.name+']' as varchar(max))))
149 + ' TO '
150 + sys.database_principals.name
151 + CASE sys.database_permissions.state_desc
152 WHEN 'GRANT' THEN ''
153 WHEN 'GRANT_WITH_GRANT_OPTION' THEN ' WITH GRANT OPTION'
154 WHEN 'DENY' THEN ' CASCADE'
155 ELSE '*' END
156 + ';'
157 AS SqlCode
158--, sys.database_permissions.state_desc
159--, sys.schemas.name as SchemaName
160--, sys.objects.name AS ObjectName
161--, sys.database_principals.name AS DatabaseUserName
162--, sys.database_permissions.permission_name collate latin1_general_cs_as
163FROM sys.database_permissions INNER JOIN
164 sys.objects ON sys.database_permissions.major_id = sys.objects.object_id INNER JOIN
165 sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id INNER JOIN
166 sys.database_principals ON sys.database_permissions.grantee_principal_id = sys.database_principals.principal_id
167WHERE sys.database_principals.name NOT IN ('public', 'guest');
168
169SET @nDbUserPermission = 1;
170SELECT @nDbUserPermission = COUNT(*) FROM @DbUserPermission
171PRINT 'PRINT ''-- - - - - - - - - - - - - - - - - - - - - - - -'''
172PRINT 'PRINT ''-- Set Object Specific Permissions '''
173SET @i = 1;
174WHILE (@i<@nDbUserPermission)
175BEGIN
176 SELECT
177 @SqlCode = SqlCode
178 , @i = @i + 1
179 FROM @DbUserPermission WHERE RowNumber = @i;
180 PRINT @SqlCode;
181END
182
183GO
SQL Script to create Logins with passwords
1
2PRINT 'PRINT ''----------------------'';';
3PRINT 'PRINT ''-- Script create logins'';';
4PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
5PRINT 'PRINT ''----------------------'';';
6PRINT 'USE Master;'
7
8<pre class="sh_SQL sh-sourceCode">
9-- <span class="sh-comment">Ref.: </span>https://support.microsoft.com/en-ca/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server<span class="sh-comment"> </span></pre>
10
11DECLARE @login_name sysname = NULL
12DECLARE @name sysname
13DECLARE @type varchar (1)
14DECLARE @hasaccess int
15DECLARE @denylogin int
16DECLARE @is_disabled int
17DECLARE @PWD_varbinary varbinary (256)
18DECLARE @PWD_string varchar (514)
19DECLARE @SID_varbinary varbinary (85)
20DECLARE @SID_string varchar (514)
21DECLARE @sql varchar (1024)
22DECLARE @is_policy_checked varchar (3)
23DECLARE @is_expiration_checked varchar (3)
24
25DECLARE @defaultdb sysname
26
27DECLARE login_curs CURSOR FOR
28SELECT p.sid
29, p.name
30, p.type
31, p.is_disabled
32, p.default_database_name
33, l.hasaccess
34, l.denylogin
35FROM sys.server_principals p
36LEFT JOIN sys.syslogins l ON ( l.name = p.name )
37WHERE
38 p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa' AND p.name NOT LIKE '##%##'
39ORDER BY p.name
40OPEN login_curs
41
42FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
43IF (@@fetch_status = -1)
44BEGIN
45 PRINT 'No login(s) found.'
46 CLOSE login_curs
47 DEALLOCATE login_curs
48 RETURN
49END
50
51PRINT ''
52WHILE (@@fetch_status <> -1)
53BEGIN
54 IF (@@fetch_status <> -2)
55 BEGIN
56 PRINT ''
57 PRINT 'PRINT ''-- Login: ' + @name + '''';
58
59 PRINT 'IF (SUSER_ID('''+QUOTENAME( @name ) +''') IS NULL) BEGIN '
60
61 IF (@type IN ( 'G', 'U'))
62 BEGIN -- NT authenticated account/group
63
64 SET @sql = ' CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
65 END
66 ELSE
67 BEGIN -- SQL Server authentication
68 -- obtain password and sid
69 SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
70 SET @PWD_string = CONVERT(NVARCHAR(MAX),@PWD_varbinary,1);
71 SET @SID_string = CONVERT(NVARCHAR(MAX),@SID_varbinary,1);
72
73 -- obtain password policy state
74 SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON'
75 WHEN 0 THEN 'OFF'
76 ELSE NULL END
77 FROM sys.sql_logins
78 WHERE
79 name = @name
80 SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON'
81 WHEN 0 THEN 'OFF'
82 ELSE NULL END
83 FROM sys.sql_logins
84 WHERE
85 name = @name
86
87 SET @sql = ' CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
88
89 IF ( @is_policy_checked IS NOT NULL )
90 BEGIN
91 SET @sql = @sql + ', CHECK_POLICY = ' + @is_policy_checked
92 END
93 IF ( @is_expiration_checked IS NOT NULL )
94 BEGIN
95 SET @sql = @sql + ', CHECK_EXPIRATION = ' + @is_expiration_checked
96 END
97 END
98 IF (@denylogin = 1)
99 BEGIN -- login is denied access
100 SET @sql = @sql + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
101 END ELSE IF (@hasaccess = 0)
102 BEGIN -- login exists but does not have access
103 SET @sql = @sql + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
104 END
105 IF (@is_disabled = 1)
106 BEGIN -- login is disabled
107 SET @sql = @sql + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
108 END
109 PRINT @sql
110 END
111 PRINT 'END;'
112 FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
113END
114CLOSE login_curs
115DEALLOCATE login_curs
SQL Script to create Logins with passwords - (safe version)
1
2PRINT 'PRINT ''----------------------'';';
3PRINT 'PRINT ''-- Script - SQL Logins'';';
4PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
5PRINT 'PRINT ''-- Server : '+ @@servername +''';';
6PRINT 'PRINT ''--'';';
7PRINT 'PRINT ''-- Warning: - if database is not ONLINE or do not exist yet, you need to execute this script again when database will be ONLINE.''';
8PRINT 'PRINT ''-- - There are warnings when Windows User no longer exists.''';
9PRINT 'PRINT ''-- - The password of SQL Users will be restored with this script.''';
10PRINT 'PRINT ''--'';';
11PRINT 'PRINT ''----------------------'';';
12PRINT 'USE Master;'
13PRINT 'PRINT ''-- Executed on Server name: ''+@@servername;'
14
15SET NOCOUNT ON;
16
17DECLARE @SqlScript TABLE ( [NumberRow] [int] IDENTITY(1,1) NOT NULL , SqlCmd nvarchar(MAX) );
18
19--#region Check Databases
20INSERT INTO @SqlScript ( SqlCmd ) VALUES ( '-- Check Databases' );
21DECLARE @templateCheckDatabase nvarchar(max) = 'IF (NOT EXISTS (SELECT [name] FROM sys.databases d WHERE (d.name = ''@name''))) BEGIN PRINT ''-- Database [@name] does NOT exist. You must run again this script after database will be created.'' END; ELSE IF (NOT EXISTS (SELECT [name] FROM sys.databases d WHERE (d.name = ''@name'') AND d.[state] = 0)) BEGIN PRINT ''-- Database [@name] does NOT ONLINE. You must run again this script after database will be ONLINE.'' END;';
22
23INSERT INTO @SqlScript ( SqlCmd )
24SELECT distinct REPLACE(@templateCheckDatabase, '@name', SP.default_database_name)
25FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL ON SP.principal_id = SL.principal_id
26WHERE SP.type IN ('S','G','U')
27AND SP.name NOT LIKE '##%##'
28AND SP.name NOT LIKE 'NT AUTHORITY%'
29AND SP.name NOT LIKE 'NT SERVICE%'
30AND SP.name <> ('sa')
31and SP.default_database_name <> 'Master';
32--#endregion Check Databases
33
34--#region SQL Logins
35INSERT INTO @SqlScript ( SqlCmd ) VALUES ( '-- SQL Logins' );
36DECLARE @templateSQLLOGIN nvarchar(MAX) = 'IF (SUSER_ID(''@loginname'') IS NULL) BEGIN PRINT ''CREATE login [@loginname]''; CREATE LOGIN [@loginname] WITH PASSWORD = @passwordLogin HASHED, CHECK_EXPIRATION = @CHECK_EXPIRATION, CHECK_POLICY = @CHECK_POLICY, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[@DEFAULT_LANGUAGE] END; ELSE BEGIN PRINT ''ALTER login [@loginname]''; ALTER LOGIN [@loginname] WITH PASSWORD = @passwordLogin HASHED, CHECK_EXPIRATION = @CHECK_EXPIRATION, CHECK_POLICY = OFF, DEFAULT_DATABASE=[@DEFAULT_DATABASE], DEFAULT_LANGUAGE=[@DEFAULT_LANGUAGE]; ALTER LOGIN [@loginname] WITH CHECK_POLICY = @CHECK_POLICY; END;';
37DECLARE @templateWindowsLOGIN nvarchar(MAX) = 'IF (SUSER_ID(''@loginname'') IS NULL) BEGIN PRINT ''CREATE login [@loginname]''; CREATE LOGIN [@loginname] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[@DEFAULT_LANGUAGE] END; ELSE BEGIN PRINT ''ALTER login [@loginname]''; ALTER LOGIN [@loginname] WITH DEFAULT_DATABASE=[@DEFAULT_DATABASE], DEFAULT_LANGUAGE=[@DEFAULT_LANGUAGE] END;';
38
39INSERT INTO @SqlScript ( SqlCmd )
40SELECT
41REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( CASE WHEN SP.type_desc = 'SQL_LOGIN' THEN @templateSQLLOGIN ELSE @templateWindowsLOGIN END ,
42'@loginname',SP.name),
43'@passwordLogin', ISNULL(CONVERT(NVARCHAR(MAX),SL.password_hash,1),'NULL')),
44'@CHECK_EXPIRATION', CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END),
45'@CHECK_POLICY', CASE WHEN SL.is_policy_checked = 1 THEN 'ON' ELSE 'OFF' END),
46'@DEFAULT_DATABASE', SP.default_database_name),
47'@DEFAULT_LANGUAGE', SP.default_language_name)
48FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL ON SP.principal_id = SL.principal_id
49WHERE SP.type IN ('S','G','U')
50AND SP.name NOT LIKE '##%##'
51AND SP.name NOT LIKE 'NT AUTHORITY%'
52AND SP.name NOT LIKE 'NT SERVICE%'
53AND SP.name <> ('sa') ORDER BY SP.type_desc , SP.name;
54--#endregion SQL Logins
55
56--#region Server Roles
57INSERT INTO @SqlScript ( SqlCmd ) VALUES ( '-- Server Roles' );
58INSERT INTO @SqlScript ( SqlCmd ) SELECT 'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + ''';' FROM master.sys.server_role_members SRM JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id WHERE SL.type IN ('S','G','U')
59AND SL.name NOT LIKE '##%##'
60AND SL.name NOT LIKE 'NT AUTHORITY%'
61AND SL.name NOT LIKE 'NT SERVICE%'
62AND SL.name <> ('sa');
63--#endregion Server Roles
64
65--#region Permissions to Be Granted
66INSERT INTO @SqlScript ( SqlCmd ) VALUES ( '-- Permissions to Be Granted' );
67INSERT INTO @SqlScript ( SqlCmd ) SELECT CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' THEN SrvPerm.state_desc ELSE 'GRANT' END + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' +
68CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' THEN '' ELSE ' WITH GRANT OPTION' END + ';' COLLATE database_default AS [-- Server Level Permissions to Be Granted --] FROM sys.server_permissions AS SrvPerm JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id WHERE SP.type IN ( 'S', 'U', 'G' )
69AND SP.name NOT LIKE '##%##'
70AND SP.name NOT LIKE 'NT AUTHORITY%'
71AND SP.name NOT LIKE 'NT SERVICE%'
72AND SP.name <> ('sa');
73--#endregion Permissions to Be Granted
74
75--#region Print Sql Script
76DECLARE @sql nvarchar(MAX);
77DECLARE @NumberRow INT;
78WHILE (EXISTS (SELECT * FROM @SqlScript)) BEGIN
79SELECT TOP 1 @NumberRow=[NumberRow] , @sql = SqlCmd FROM @SqlScript ORDER BY [NumberRow];
80DELETE FROM @SqlScript WHERE [NumberRow]=@NumberRow;
81PRINT @sql;
82END
83--#endregion Print Sql Script
Comments