SHARE
TWEET

Create login scripts that include SID

a guest Jan 29th, 2015 284 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE master
  2. GO
  3. IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  4.   DROP PROCEDURE sp_hexadecimal
  5. GO
  6. CREATE PROCEDURE sp_hexadecimal
  7.     @binvalue varbinary(256),
  8.     @hexvalue varchar (514) OUTPUT
  9. AS
  10. DECLARE @charvalue varchar (514)
  11. DECLARE @i int
  12. DECLARE @length int
  13. DECLARE @hexstring char(16)
  14. SELECT @charvalue = '0x'
  15. SELECT @i = 1
  16. SELECT @length = DATALENGTH (@binvalue)
  17. SELECT @hexstring = '0123456789ABCDEF'
  18. WHILE (@i <= @length)
  19. BEGIN
  20.   DECLARE @tempint int
  21.   DECLARE @firstint int
  22.   DECLARE @secondint int
  23.   SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  24.   SELECT @firstint = FLOOR(@tempint/16)
  25.   SELECT @secondint = @tempint - (@firstint*16)
  26.   SELECT @charvalue = @charvalue +
  27.     SUBSTRING(@hexstring, @firstint+1, 1) +
  28.     SUBSTRING(@hexstring, @secondint+1, 1)
  29.   SELECT @i = @i + 1
  30. END
  31.  
  32. SELECT @hexvalue = @charvalue
  33. GO
  34.  
  35. IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  36.   DROP PROCEDURE sp_help_revlogin
  37. GO
  38. CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
  39. DECLARE @name sysname
  40. DECLARE @type varchar (1)
  41. DECLARE @hasaccess int
  42. DECLARE @denylogin int
  43. DECLARE @is_disabled int
  44. DECLARE @PWD_varbinary  varbinary (256)
  45. DECLARE @PWD_string  varchar (514)
  46. DECLARE @SID_varbinary varbinary (85)
  47. DECLARE @SID_string varchar (514)
  48. DECLARE @tmpstr  varchar (1024)
  49. DECLARE @is_policy_checked varchar (3)
  50. DECLARE @is_expiration_checked varchar (3)
  51.  
  52. DECLARE @defaultdb sysname
  53.  
  54. IF (@login_name IS NULL)
  55.   DECLARE login_curs CURSOR FOR
  56.  
  57.       SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
  58. sys.server_principals p LEFT JOIN sys.syslogins l
  59.       ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
  60. ELSE
  61.   DECLARE login_curs CURSOR FOR
  62.  
  63.  
  64.       SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
  65. sys.server_principals p LEFT JOIN sys.syslogins l
  66.       ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
  67. OPEN login_curs
  68.  
  69. FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
  70. IF (@@fetch_status = -1)
  71. BEGIN
  72.   PRINT 'No login(s) found.'
  73.   CLOSE login_curs
  74.   DEALLOCATE login_curs
  75.   RETURN -1
  76. END
  77. SET @tmpstr = '/* sp_help_revlogin script '
  78. PRINT @tmpstr
  79. SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
  80. PRINT @tmpstr
  81. PRINT ''
  82. WHILE (@@fetch_status <> -1)
  83. BEGIN
  84.   IF (@@fetch_status <> -2)
  85.   BEGIN
  86.     PRINT ''
  87.     SET @tmpstr = '-- Login: ' + @name
  88.     PRINT @tmpstr
  89.     IF (@type IN ( 'G', 'U'))
  90.     BEGIN -- NT authenticated account/group
  91.  
  92.       SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
  93.     END
  94.     ELSE BEGIN -- SQL Server authentication
  95.         -- obtain password and sid
  96.             SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
  97.         EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
  98.         EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
  99.  
  100.         -- obtain password policy state
  101.         SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
  102.         SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
  103.  
  104.             SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
  105.  
  106.         IF ( @is_policy_checked IS NOT NULL )
  107.         BEGIN
  108.           SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
  109.         END
  110.         IF ( @is_expiration_checked IS NOT NULL )
  111.         BEGIN
  112.           SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
  113.         END
  114.     END
  115.     IF (@denylogin = 1)
  116.     BEGIN -- login is denied access
  117.       SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
  118.     END
  119.     ELSE IF (@hasaccess = 0)
  120.     BEGIN -- login exists but does not have access
  121.       SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
  122.     END
  123.     IF (@is_disabled = 1)
  124.     BEGIN -- login is disabled
  125.       SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
  126.     END
  127.     PRINT @tmpstr
  128.   END
  129.  
  130.   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
  131.    END
  132. CLOSE login_curs
  133. DEALLOCATE login_curs
  134. RETURN 0
  135. GO
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top