|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
CREATE PROCEDURE dbo.sp_help_revlogin_serverroles AS /******************************************************************************* Script out server roles (SQL 2012+). Script out adding logins to server roles. SQL 2012+: Use ALTER SERVER ROLE Prior: Use master..sp_addsrvrolemember ******************************************************************************** MODIFICATION LOG ******************************************************************************** 2012-10-17 WGS Initial Creation. 2014-01-08 WGS Proper quoting in the print statements. *******************************************************************************/ DECLARE @SqlCmd VARCHAR(MAX); -- Create the server roles if they don't exist. -- SQL 2012 and higher IF CONVERT(INT, PARSENAME(CONVERT(VARCHAR(20), SERVERPROPERTY('ProductVersion')), 4)) >= 11 -- SQL 2012 or higher SET @SqlCmd = 'DECLARE @SqlCmd VARCHAR(1000); PRINT ''--Ensuring server roles exist...''; DECLARE cServerRoles CURSOR FOR SELECT ''IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = '''''' + name + '''''' AND type = ''''R'''') AND CONVERT(INT, PARSENAME(CONVERT(VARCHAR(20), SERVERPROPERTY(''''ProductVersion'''')), 4)) >= 11 BEGIN PRINT ''''Adding Server Role: '' + QUOTENAME(name) + ''''''; EXECUTE (''''CREATE SERVER ROLE '' + QUOTENAME(name) + ISNULL(ca1.Auth, '''') + ''''''); END; '' FROM sys.server_principals sp1 OUTER APPLY (SELECT '' AUTHORIZATION '' + QUOTENAME(name) FROM sys.server_principals WHERE principal_id = sp1.owning_principal_id) ca1(Auth) WHERE type = ''R'' ORDER BY name; OPEN cServerRoles; FETCH NEXT FROM cServerRoles INTO @SqlCmd; WHILE @@FETCH_STATUS = 0 BEGIN PRINT @SqlCmd; FETCH NEXT FROM cServerRoles INTO @SqlCmd; END; CLOSE cServerRoles; DEALLOCATE cServerRoles; ' -- PRINT @SqlCmd; EXECUTE (@SqlCmd); -- add logins to the roles PRINT ' --Now add logins to the server roles'; DECLARE cLoginRoles CURSOR FOR SELECT AddLoginToRoleCmd = 'IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = ''' + ca1.RoleName + ''' AND type = ''R'') AND EXISTS (SELECT 1 FROM sys.server_principals WHERE name = ''' + ca2.LoginName + ''') BEGIN PRINT ''Adding login ''''' + ca2.LoginName + ''''' to server role ''''' + ca1.RoleName + '''''''; IF CONVERT(INT, PARSENAME(CONVERT(VARCHAR(20), SERVERPROPERTY(''ProductVersion'')),4)) >= 11 EXECUTE (''ALTER SERVER ROLE ' + QUOTENAME(ca1.RoleName) + ' ADD MEMBER ' + QUOTENAME(ca2.LoginName) + '''); ELSE EXECUTE master..sp_addsrvrolemember @loginame = N''' + ca2.LoginName + ''', @rolename = N''' + ca1.RoleName + '''; END ELSE BEGIN PRINT ''Cannot add login ''''' + ca2.LoginName + ''''' to role ''''' + ca1.RoleName + ''''' (either login or role does not exist)'' END; ' FROM sys.server_role_members srm CROSS APPLY (SELECT name FROM sys.server_principals WHERE principal_id = srm.role_principal_id) ca1 (RoleName) CROSS APPLY (SELECT name FROM sys.server_principals WHERE principal_id = srm.member_principal_id) ca2 (LoginName) ORDER BY ca2.LoginName, ca1.RoleName; OPEN cLoginRoles; FETCH NEXT FROM cLoginRoles INTO @SqlCmd; WHILE @@FETCH_STATUS = 0 BEGIN PRINT @SqlCmd; FETCH NEXT FROM cLoginRoles INTO @SqlCmd; END; CLOSE cLoginRoles; DEALLOCATE cLoginRoles; GO |
(2017-2022)
- Comments (0)
- Trackbacks (0)
No one has commented yet.
No trackbacks yet.
