Mudando de Dedicado? Aprenda a exportar os logins do SQLServer - 4FunBR

Mudando de Dedicado? Aprenda a exportar os logins do SQLServer

Conheça uma função que exporta os logins e as permissões

02 de Outubro de 2013 às 23:34

Quem precisou mudar de dedicado e utilizava o banco SQL Server sabe como é ruim ter que ficar excluindo os usuários de banco e recriando no dedicado novo. Para ajudar nesta transição seguem algumas procedures que nós utilizamos neste processo:

Exportando Logins no SQL Server 2005 ou inferior

 USE master
 GO
 IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
   DROP PROCEDURE sp_hexadecimal
 GO
 CREATE PROCEDURE sp_hexadecimal
  @binvalue varbinary(256),
  @hexvalue varchar(256) OUTPUT
 AS
 DECLARE @charvalue varchar(256)
 DECLARE @i int
 DECLARE @length int
 DECLARE @hexstring char(16)
 SELECT @charvalue = '0x'
 SELECT @i = 1
 SELECT @length = DATALENGTH (@binvalue)
 SELECT @hexstring = '0123456789ABCDEF'
 WHILE (@i <= @length)
 BEGIN
   DECLARE @tempint int
   DECLARE @firstint int
   DECLARE @secondint int
   SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
   SELECT @firstint = FLOOR(@tempint/16)
   SELECT @secondint = @tempint - (@firstint*16)
   SELECT @charvalue = @charvalue +
  SUBSTRING(@hexstring, @firstint+1, 1) +
  SUBSTRING(@hexstring, @secondint+1, 1)
   SELECT @i = @i + 1
 END
 SELECT @hexvalue = @charvalue
 GO

 IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
   DROP PROCEDURE sp_help_revlogin
 GO
 CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
 DECLARE @name    sysname
 DECLARE @xstatus int
 DECLARE @binpwd  varbinary (256)
 DECLARE @txtpwd  sysname
 DECLARE @tmpstr  varchar (256)
 DECLARE @SID_varbinary varbinary(85)
 DECLARE @SID_string varchar(256)

 IF (@login_name IS NULL)
   DECLARE login_curs CURSOR FOR
  SELECT sid, name, xstatus, password FROM master..sysxlogins
  WHERE srvid IS NULL AND name <> 'sa'
 ELSE
   DECLARE login_curs CURSOR FOR
  SELECT sid, name, xstatus, password FROM master..sysxlogins
  WHERE srvid IS NULL AND name = @login_name
 OPEN login_curs
 FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
 IF (@@fetch_status = -1)
 BEGIN
   PRINT 'No login(s) found.'
   CLOSE login_curs
   DEALLOCATE login_curs
   RETURN -1
 END
 SET @tmpstr = '/* sp_help_revlogin script '
 PRINT @tmpstr
 SET @tmpstr = '** Generated '
   + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
 PRINT @tmpstr
 PRINT ''
 PRINT 'DECLARE @pwd sysname'
 WHILE (@@fetch_status <> -1)
 BEGIN
   IF (@@fetch_status <> -2)
   BEGIN
  PRINT ''
  SET @tmpstr = '-- Login: ' + @name
  PRINT @tmpstr
  IF (@xstatus & 4) = 4
  BEGIN -- NT authenticated account/group
    IF (@xstatus & 1) = 1
    BEGIN -- NT login is denied access
   SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
   PRINT @tmpstr
    END
    ELSE BEGIN -- NT login has access
   SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
   PRINT @tmpstr
    END
  END
  ELSE BEGIN -- SQL Server authentication
    IF (@binpwd IS NOT NULL)
    BEGIN -- Non-null password
   EXEC sp_hexadecimal @binpwd, @txtpwd OUT
   IF (@xstatus & 2048) = 2048
     SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
   ELSE
     SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
   PRINT @tmpstr
  EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
   SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''
     + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
    END
    ELSE BEGIN
   -- Null password
  EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
   SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''
     + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
    END
    IF (@xstatus & 2048) = 2048
   -- login upgraded from 6.5
   SET @tmpstr = @tmpstr + '''skip_encryption_old'''
    ELSE
   SET @tmpstr = @tmpstr + '''skip_encryption'''
    PRINT @tmpstr
  END
   END
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
   END
 CLOSE login_curs
 DEALLOCATE login_curs
 RETURN 0
 GO
 IF OBJECT_ID ('seeMigrateSQLLogins') IS NOT NULL
   DROP PROCEDURE seeMigrateSQLLogins
 GO
 create procedure seeMigrateSQLLogins @login_name sysname = NULL
 as
 declare
  @name char(50),
  @binpwd  varbinary (256),
  @txtpwd  sysname,
  @tmpstr  varchar (256),
  @SID_varbinary varbinary(85),
  @SID_string varchar(256),
  @Is_Policy bit,
  @Is_Exp bit,
  @type char(1),
  @Pol char(3),
  @Exp char(3)
 set nocount on
 create table #logins (
  [name] nvarchar(128) NOT NULL,
  [sid] varbinary(85) NOT NULL,
  [type] char(1) NOT NULL,
  [is_policy_checked] bit default 0,
  [is_expiration_checked] bit default 0,
  [password_hash] varbinary(256) )
 insert #logins (name, sid, type)
 select name, sid, type from sys.server_principals where 
  (type_desc = 'SQL_LOGIN' or type_desc = 'WINDOWS_LOGIN') and name <> 'sa' and name <> 'NT AUTHORITYSYSTEM'
 update a set a.is_policy_checked = b.is_policy_checked, a.is_expiration_checked = b.is_expiration_checked, a.password_hash = b.password_hash
  from #logins a, sys.sql_Logins b
  where a.sid = b.sid
 set nocount off
 IF (@login_name IS NULL) --Not a single user, get the list
   DECLARE seelogin_curs CURSOR FOR
  SELECT name, sid, password_hash, type, is_policy_checked, is_expiration_checked FROM #logins
  WHERE name <> 'sa'
 ELSE
   DECLARE seelogin_curs CURSOR FOR
  SELECT name, sid, password_hash, type, is_policy_checked, is_expiration_checked FROM #logins
  WHERE name = @login_name
 OPEN seelogin_curs
 FETCH NEXT FROM seelogin_curs INTO @name, @SID_varbinary, @binpwd, @type, @Is_Policy, @Is_Exp
 IF (@@fetch_status = -1)
 BEGIN
   PRINT 'No login(s) found.'
   CLOSE seelogin_curs
   DEALLOCATE seelogin_curs
 END
 SET @tmpstr = '/* seeMigrateSQLLogins - For SQL Server 2005 Only '
 PRINT @tmpstr
 SET @tmpstr = '** Generated '
   + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
 PRINT @tmpstr
 PRINT ''
 WHILE (@@fetch_status <> -1)
 BEGIN
  IF @type = 'S'
   BEGIN
    PRINT '/* SQL Login ******************/'
    EXEC sp_hexadecimal @binpwd, @txtpwd OUT
    EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
    IF @Is_Policy = 1 Begin set @Pol = 'ON' End ELSE Begin set @Pol = 'OFF' END 
    IF @Is_Exp = 1 Begin set @Exp = 'ON' End ELSE Begin set @Exp = 'OFF' END 
    SET @tmpstr = 'Create Login [' + rtrim(@name) + '] WITH PASSWORD = ' + @txtpwd + ' hashed, sid = ' + @SID_string + ', CHECK_POLICY = ' + @Pol + ', CHECK_EXPIRATION = ' + @Exp
    PRINT @tmpstr
    PRINT ''
   END
  Else
   BEGIN
    PRINT '/* SQL Login ******************/'
    EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
    SET @tmpstr = 'Create Login [' + rtrim(@name) + '] FROM WINDOWS; '
    PRINT @tmpstr
    PRINT ''
   END
 FETCH NEXT FROM seelogin_curs INTO @name, @SID_varbinary, @binpwd, @type, @Is_Policy, @Is_Exp
 END
 CLOSE seelogin_curs
 DEALLOCATE seelogin_curs
 drop table #logins
 GO

 declare
 @version2005 char(5)
 declare
 @version2008 char(5)

 --Get the current version of SQL Server running
 select @version2005 = substring(@@version,29,4)
 select @version2008 = substring(@@version,35,4)

 if @version2005 = '9.00'
  Begin
   exec seeMigrateSQLLogins
  End
 Else if @version2008 = '10.0'
  Begin
   exec seeMigrateSQLLogins
  End
 Else
  begin
   exec sp_help_revlogin
  End

 IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
   DROP PROCEDURE sp_hexadecimal
 GO
 IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
   DROP PROCEDURE sp_help_revlogin
 GO
 IF OBJECT_ID ('seeMigrateSQLLogins') IS NOT NULL
   DROP PROCEDURE seeMigrateSQLLogins
 GO
/* End Script */


USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO
 
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname
 
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
 
        -- obtain password policy state
        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
        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
 
            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

Exportando Logins no SQL Server 2008 ou superior

USE master
GO 

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO

CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO

CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR
      SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin
        FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )
        WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR
      SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin
        FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )
        WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name

OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr

    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group
      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
        SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT

        -- obtain password policy state
        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
        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

        SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name )
            + ' WITH PASSWORD = ' + @PWD_string
            + ' HASHED, SID = ' + @SID_string
        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
 
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login has exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END

    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END
  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin
  END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

exec sp_help_revlogin;
GO

Exportando permissões

SELECT 
   CASE dp.state_desc 
     WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT' 
     ELSE dp.state_desc  
   END  
     + ' ' + dp.permission_name + ' ON ' + 
   CASE dp.class 
     WHEN 0 THEN 'DATABASE::[' + DB_NAME() + ']' 
     WHEN 1 THEN 'OBJECT::[' + SCHEMA_NAME(o.schema_id) + '].[' + o.[name] + ']' 
     WHEN 3 THEN 'SCHEMA::[' + SCHEMA_NAME(dp.major_id) + ']' 
   END  
     + ' TO [' + USER_NAME(grantee_principal_id) + ']' + 
   CASE dp.state_desc 
     WHEN 'GRANT_WITH_GRANT_OPTION' THEN ' WITH GRANT OPTION;' 
     ELSE ';'  
   END  
   COLLATE DATABASE_DEFAULT 
FROM sys.database_permissions dp 
  LEFT JOIN sys.all_objects o 
    ON dp.major_id = o.OBJECT_ID 
WHERE dp.class < 4 
  AND major_id >= 0 
  AND grantee_principal_id <> 1;

4FunBR - Painel para Servidores de Jogos

Blog

Exportando Logins no SQL Server 2005 ou inferior

Exportando Logins no SQL Server 2008 ou superior

Exportando permissões

Mais publicações