jueves, 5 de noviembre de 2015

Aplicando un Filtro a sp_MSforeachDB

Problema
Cuando se necesita trabajar sobre multiples bases de datos en una misma instnacia, a veces, se necesita ejecutar una consulta en cada una de estas, sí estamos utilizando el procedimiento almacenado no documentado sp_MSforeachDB, nos encontraremos en ciertos casos en la obligación de excluir o filtrar algunas de estas bases datos, por ejemplos las bases de datos del sistema (master, tempdb, model y msdb) o cualquier otra base de datos que por una razón u otra no aplique para el caso.


Solución
USE MASTER;
GO
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''master'',''tempdb'',''model'',''msdb'')
BEGIN
       SELECT name, physical_name,state,size
       FROM ?.sys.database_files
END';

GO


Referencias

martes, 28 de abril de 2015

Cómo iterar en un conjunto de resultados mediante T-SQL en SQL Server

Problema:

Describir los diferentes métodos que se pueden usar para simular la lógica iterativa de un cursor en un procedimiento almacenado (stored procedure), disparador (trigger) o lote (batch) de T-SQL.

Solución:

Existen tres métodos para iterar a través de un conjunto de resultados utilizando sentencias Transact-SQL.

Un método es el uso de tablas temporales. Con este método, usted crea una “instantánea” (snapshot) de la sentencia SELECT inicial y utilizar esta como base sobre la cual iterar. Por ejemplo:

/********** ejemplo 1 **********/ 
DECLARE @au_id CHAR( 11 )

SET ROWCOUNT 0
SELECT * INTO #mytemp FROM authors

SET ROWCOUNT 1

SELECT @au_id = au_id FROM #mytemp

WHILE @@rowcount <> 0
BEGIN
    SET ROWCOUNT 0
    
SELECT * FROM #mytemp WHERE au_id = @au_id
    
DELETE #mytemp WHERE au_id = @au_id

    
SET ROWCOUNT 1
    
SELECT @au_id = au_id FROM #mytemp

END
SET
ROWCOUNT 0
GO


Un segundo método es utilizar la función min para caminar una tabla una fila a la vez. Este método toma en cuenta las filas que se agregan después de la ejecución del procedimiento almacenado empieza, si la fila nueva posee un identificador único mayor que el de la fila que se está siendo procesada en dicho momento por la consulta. Por ejemplo:

/********** ejemplo 2 **********/ 

DECLARE @au_id CHAR( 11 )

SELECT @au_id = MIN( au_id ) FROM authors

WHILE @au_id IS NOT NULL
BEGIN
    SELECT
* FROM authors WHERE au_id = @au_id
    
SELECT @au_id = MIN( au_id ) FROM authors WHERE au_id > @au_id

END
GO

NOTA: ambos ejemplos 1 y 2 asumen que una columna de identificador único existe para cada fila en la tabla fuente. En algunos casos, dicho identificador no existe. Si ese es el caso, usted puede modificar el método de tabla temporal para usar una columna identificadora nueva. Por ejemplo:

/********** ejemplo 3 **********/ 

SET ROWCOUNT 0
SELECT NULL mykey, * INTO #mytemp FROM authors

SET ROWCOUNT 1
UPDATE #mytemp SET mykey = 1

WHILE @@rowcount > 0
BEGIN
    SET
ROWCOUNT 0
    
SELECT * FROM #mytemp WHERE mykey = 1
    
DELETE #mytemp WHERE mykey = 1
    
SET ROWCOUNT 1
    
UPDATE #mytemp SET mykey = 1

END
SET
ROWCOUNT 0
GO


Referencias:

How to iterate through a result set by using Transact-SQL in SQL Server

Script para identificar los índices que no están siendo utilizados en SQL Server

Problema:

Script para identificar los índices que no están siendo utilizados en SQL Server.

Solución:

SELECT TOP 25
    o.name
AS ObjectName, 

    i.name AS IndexName, 
    i.index_id AS IndexID, 
    dm_ius.user_seeks AS UserSeek, 
    dm_ius.user_scans AS UserScans, 
    dm_ius.user_lookups AS UserLookups, 
    dm_ius.user_updates AS UserUpdates, 
    p.TableRows, 
    'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' +                    
    QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
    INNER JOIN sys.indexes i 
        ON i.index_id = dm_ius.index_id 
        AND dm_ius.OBJECT_ID = i.OBJECT_ID
    INNER JOIN sys.objects o 
        ON dm_ius.OBJECT_ID = o.OBJECT_ID
    INNER JOIN sys.schemas s 
        ON o.schema_id = s.schema_id
    INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
                FROM sys.partitions p 
                GROUP BY p.index_id, p.OBJECT_ID) p
        ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1 
    AND dm_ius.database_id = DB_ID()
    AND
i.type_desc = 'nonclustered'

    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
GO


Referencias:


SQL SERVER – 2008 – Unused Index Script – Download

Scripts para obtener la información de seguridad de SQL Server

Problema:

Encontrar la información de seguridad referente a los accesos (logins), usuarios y roles y permisos a nivel de objetos en las bases de datos en SQL Server.

Solución:

Script que obtiene los logins de servidor y los roles asignados a estos:

SELECTa.name AS LoginName,a.type_desc AS LoginType, a.default_database_name AS DefaultDBName,CASE
    
WHEN b.sysadmin = 1 THEN 'sysadmin'
    
WHEN b.securityadmin=1 THEN 'securityadmin'
    
WHEN b.serveradmin=1 THEN 'serveradmin'
    
WHEN b.setupadmin=1 THEN 'setupadmin'
    
WHEN b.processadmin=1 THEN 'processadmin'
    
WHEN b.diskadmin=1 THEN 'diskadmin'
    
WHEN b.dbcreator=1 THEN 'dbcreator'
    
WHEN b.bulkadmin=1 THEN 'bulkadmin'ELSE 'Public' END AS 'ServerRole'FROM sys.server_principals a  JOIN MASTER..syslogins b
    
ON a.sid=b.sid WHERE a.TYPE  <> 'R'
      
AND a.name NOT LIKE '##%'

GO

Script que obtiene los usuarios de base de datos y los roles asignados a estos:

DECLARE @DBuser_sql VARCHAR(4000);DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200));

SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a
LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'
;


INSERT @DBuser_tableEXEC sp_MSforeachdb @command1 = @dbuser_sql
  
SELECT * FROM @DBuser_table ORDER BY DBName;

GO

Script que obtiene los permisos a nivel de objeto de los usuarios de base de datos:

DECLARE @Obj_sql VARCHAR(2000);
DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), ObjectName VARCHAR(500), Permission VARCHAR(200));

SET @Obj_sql='select ''?'' as DBName,U.name as username, O.name as object,  permission_name as permission from ?.sys.database_permissions
join ?.sys.sysusers U on grantee_principal_id = uid join ?.sys.sysobjects O on major_id = id WHERE ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') order by U.name '
;


INSERT @Obj_table
  
EXEC sp_msforeachdb @command1 = @Obj_sql
      
SELECT * FROM @Obj_table;

GO


Referencias:

SCRIPT TO RETRIEVE SECURITY INFORMATION SQL SERVER 2005 AND ABOVE

Encontrar el folder por defecto para las copias de respaldo (backups) en SQL Server.

Problema:

Encontrar el folder por defecto para las copias de respaldo (backups) en SQL Server.

Solución:

DECLARE @ruta NVARCHAR(4000);

EXEC MASTER.dbo.xp_instance_regread
        
N'HKEY_LOCAL_MACHINE',
        
N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',
        
@ruta OUTPUT, 
        
'no_output';
SELECT @ruta;
   

Referencias:

Function to Return Default SQL Server Backup Folder

Obtener información sobre núcleos físicos, CPUs físicos y virtuales y tipo de procesador en SQL Server utilizando T-SQL

Descripción:

Script para obtener la información sobre los procesadores disponibles para ser consumidos por SQL Server:
  • Número total de CPUs físicos
  • Número total de núcleos por CPU
  • Número total de núcleos físicos
  • Número total de núcleos virtuales
  • Tipo de procesador (x86 o x64)
Solución:

DECLARE @xp_msver TABLE (
    
[idx] [int] NULL
    ,
[c_name] [varchar](100) NULL
    ,
[int_val] [float] NULL
    ,
[c_val] [varchar](128) NULL
    )
INSERT INTO @xp_msverEXEC ('[master]..[xp_msver]');;
WITH [ProcessorInfo]AS (
    
SELECT ([cpu_count] / [hyperthread_ratio]) AS [number_of_physical_cpus]
        
,CASE
            
WHEN hyperthread_ratio = cpu_count
                
THEN cpu_count
            
ELSE (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
            
END AS [number_of_cores_per_cpu]
        
,CASE
            
WHEN hyperthread_ratio = cpu_count
                
THEN cpu_count
            
ELSE ([cpu_count] / [hyperthread_ratio]) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
            
END AS [total_number_of_cores]
        
,[cpu_count] AS [number_of_virtual_cpus]
        
,(
            
SELECT [c_val]
            
FROM @xp_msver
            
WHERE [c_name] = 'Platform'
            
) AS [cpu_category]
    
FROM [sys].[dm_os_sys_info]
    
)SELECT [number_of_physical_cpus]
    
,[number_of_cores_per_cpu]
    
,[total_number_of_cores]
    
,[number_of_virtual_cpus]
    
,LTRIM(RIGHT([cpu_category], CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category]FROM [ProcessorInfo]


Referencias:

Get SQL Server Physical Cores, Physical and Virtual CPUs, and Processor type information using Transact-SQL (T-SQL) script

viernes, 20 de febrero de 2015

Obtener la cuenta de servicio de Microsoft SQL Server con T-SQL

Descripción:
La información de la cuenta del servicio de SQL Server es almacenada en la base de datos del Registro de Windows. Usted puede acceder a dicha información desde la Consola de Servicios (Services Console) o el Administrador de Configuración de SQL Server (SQL Server Configuration Manager).

Solución:
Para obtener la información de la cuenta desde la Consola de Servicios:
  1. Ir a Inicio > Ejecutar > services.msc
  2. Click derecho sobre el Servicio SQL Server, ej. "SQL Server (InstanceName) e ir a Propiedades
  3. La información de la cuenta esta disponible en la pestaña Log On:

También podemos obtener esta información usando T-SQL, podemos utilizar el procedimiento almacenado extendido del sistema xp_instance_regread para leer el Registro de Windows. Utilice el código siguiente para obtener la información de las cuentas del los servicios SQL Server y SQL Server Agent:

DECLARE       @sql_server_service_account         SYSNAME;DECLARE       @sql_server_agent_service_account   SYSNAME;
EXECUTE       MASTER.dbo.xp_instance_regread
              
@rootkey      = N'HKEY_LOCAL_MACHINE',
              
@key          = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
              
@value_name   = N'ObjectName',
              
@value        = @sql_server_service_account OUTPUT
EXECUTE       MASTER.dbo.xp_instance_regread
              
@rootkey      = N'HKEY_LOCAL_MACHINE',
              
@key          = N'SYSTEM\CurrentControlSet\Services\SQLServerAgent',
              
@value_name   = N'ObjectName',
              
@value        = @sql_server_agent_service_account OUTPUT
SELECT [SQL Server Service Account] = @sql_server_service_account,
      
[SQL Server Agent Service Account] = @sql_server_agent_service_account;GO


Desde Microsoft SQL Server 2008 R2 SP1 y superior una nueva vista de administración dinámica (DMV) esta disponible sys.dm_server_services la cual retorna la información de las cuentas de servicio de todas las instancia. Esta vista también retorna información adicional acerca de cada servicio como tipo de inicio, process id actual, nombre físico del ejecutable. Para consultar espec&iacuteficamente las cuentas de los servicios SQL Server y SQL Server Agent con esta vista podemos utilizar la siguiente consulta:

SELECT servicename, service_accountFROM   sys.dm_server_services
GO


Referencias

jueves, 19 de febrero de 2015

Crecimiento del tamaño de la base de datos como una lista

Descripción:

Este script de Transact-SQL usa la historia de copias de respaldo para analizar el crecimiento del tamaño de las bases de datos sobre un periodo determinado de tiempo. También se calcula adicionalmente el mínimo, máximo y promedio de crecimiento de tamaño mensual en relación al mes anterior. Estos valores son útiles para el planeamiento futuro de recursos de almacenamiento y sistemas de copias de seguridad. Este script trabaja en Microsoft SQL Server 2005 y versiones superiores en todas las ediciones.


Requerimientos:

  • Este script requiere acceso y permiso de lectura sobre la base de datos del sistema msdb.


Solución:

-- Script T-SQL para analizar el crecimiento de tamaño de la base de datos utilizando la historia de copias de respaldo.
DECLARE @endDate DATETIME, @months smallint;
SET @endDate = GETDATE();    -- Incluir las estadísticas de las copias de respaldo de hoy.
SET @months = 6;            -- hasta 6 meses atrás.

;WITH HIST AS
  
(SELECT BS.database_name AS DatabaseName
          
,YEAR(BS.backup_start_date) * 100
          
+ MONTH(BS.backup_start_date) AS YearMonth
          
,CONVERT(numeric(10, 1), MIN(BF.file_size / 1048576.0)) AS MinSizeMB
          
,CONVERT(numeric(10, 1), MAX(BF.file_size / 1048576.0)) AS MaxSizeMB
          
,CONVERT(numeric(10, 1), AVG(BF.file_size / 1048576.0)) AS AvgSizeMB
    
FROM msdb.dbo.backupset AS BS
        
INNER JOIN
        
msdb.dbo.backupfile AS BF
            
ON BS.backup_set_id = BF.backup_set_id
    
WHERE NOT BS.database_name IN
              
('master', 'msdb', 'model', 'tempdb')
          AND
BF.file_type = 'D'
          
AND BS.backup_start_date BETWEEN DATEADD(mm, - @months, @endDate) AND @endDate
    
GROUP BY BS.database_name
            
,YEAR(BS.backup_start_date)
            ,
MONTH(BS.backup_start_date))SELECT MAIN.DatabaseName
      
,MAIN.YearMonth
      
,MAIN.MinSizeMB
      
,MAIN.MaxSizeMB
      
,MAIN.AvgSizeMB
      
,MAIN.AvgSizeMB
      
- (SELECT TOP 1 SUB.AvgSizeMB
          
FROM HIST AS SUB
          
WHERE SUB.DatabaseName = MAIN.DatabaseName
                
AND SUB.YearMonth < MAIN.YearMonth
          
ORDER BY SUB.YearMonth DESC) AS GrowthMBFROM HIST AS MAINORDER BY MAIN.DatabaseName
        
,MAIN.YearMonth;
GO    



Referencias

Mover los archivos de base de datos de SQL Server a otra ubicación vía T-SQL.

SQL Server 2000

-- Cambiar al contexto de la base de datos master
USE MASTER;
GO

-- Llevar la base de datos a single user mode
-- Esto termina todas las conexiones a la base de datos
ALTER DATABASE dbTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- Desacoplar la base de datos
EXEC MASTER.dbo.sp_detach_db @dbname = N'dbTest'
GO

--
-- Mover el o los archivos físicos de la base de datos manualmente a la nueva ruta.
-- ej.: x:\move e:\dbTest.mdf e:\NuevaRuta\dbTest.mdf
--

-- Re-acoplar la base de datos
CREATE DATABASE [TestDB] ON
(FILENAME = N'E:\RutaNueva\dbTest.mdf' ),
(
FILENAME = N'E:\RutaNueva\dbTest_log.ldf' )
FOR ATTACH
GO

SQL Server 2005, 2008, 2008 R2 o superior (Misma instancia)

-- Cambiar al contexto de la base de datos master
USE MASTER;
GO

-- Cambiar el estado de la base de datos a modo de usuario único
ALTER DATABASE dbTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- Cambiar el estado de la base de datos a fuera de línea
ALTER DATABASE dbtest SET OFFLINE;
GO

--
-- Mover el o los archivos físicos de la base de datos manualmente a la nueva ruta.
-- ej.: x:\move e:\dbTest.mdf e:\NuevaRuta\dbTest.mdf
--

-- Re-apunta la ruta del archivo mdf a la nueva ubicación
ALTER DATABASE dbTest
MODIFY
FILE
  
(NAME='dbTest', FILENAME='E:\NuevaRuta\dbTest.mdf');

-- Re-apunta la ruta del archivo ldf a la nueva ubicación
ALTER DATABASE dbTest
MODIFY
FILE
  
(NAME='dbTest_Log', FILENAME='E:\NuevaRuta\dbTest_log.ldf');
GO

-- Cambiar el estado de la base de datos a en-línea
ALTER DATABASE TEMP SET ONLINE;
GO

-- Cambiar el estado de la base de datos a modo multi-usuario
ALTER DATABASE TEMP SET MULTI_USER;
GO

SQL Server 2005, 2008, 2008 R2 o superior (Instancia diferente)

-- Cambiar al contexto de la base de datos master
USE MASTER;
GO

-- Desacoplar la base de datos
EXEC sp_detach_db @dbname = N'dbTest';

--
-- Mover el o los archivos físicos de la base de datos manualmente a la nueva ruta.
-- ej.: x:\move e:\dbTest.mdf e:\NuevaRuta\dbTest.mdf
--

-- Reacoplar la base de datos con los archivos en la nueva instancia y nueva ubicación
CREATE DATABASE dbTest
      
ON(NAME='dbTest',
            
FILENAME='e:\NuevaRuta\dbTest.mdf')
      
LOG ON(NAME='MyDatabase_Log',
            
FILENAME='e:\NuevaRuta\dbTest_log.ldf')
      
FOR ATTACH
      
WITH ENABLE_BROKER;
GO

Referencias

martes, 5 de noviembre de 2013

Creando un Servidor Vinculado de SQL Server a SQLite para Importar Datos

Problema

En la actualidad utilizamos y desarrollamos un sinnúmero de aplicaciones basadas en tecnologías móviles y el motor de base de datos por excelencia para este tipo de aplicaciones es SQLite, líder indiscutible en este segmento de tecnológico. En ocasiones nos veremos obligados a importar datos desde SQLite hacia SQL Server, y este artículo tiene como finalidad mostrar una guía paso a paso de cómo realizar esta tarea que ha cobrado tanta importancia en estos momentos donde el auge de las aplicaciones para dispositivos móviles crece a un ritmo muy acelerado.

Solución

Existen varias formas alternativas de como importar los datos desde SQLite hacia SQL Server, nosotros como solución implementaremos un Linked Server hacia SQLite.

Dividiremos esta solución en los siguientes pasos:

  1. Descargar un controlador ODBC para SQLite
  2. Instalar el controlador
  3. Crear un DSN a nivel de sistema para la base de datos en SQLite
  4. Crear el Linked Server en SQL Server
  5. Seleccionar los datos de la fuente e insertarlos en nuestra tabla en SQL Server

  1. Descargar un controlador ODBC para SQLite
  2. Ir a esta página donde se encuentra el controlador ODBC para SQLite. Configurar el controlador correcto es algunas veces la parte más difícil, por lo que recomendamos descargar ambos controladores tanto el de 32 como el de 64 bits.

  3. Instalar el controlador
  4. Elija el controlador que le corresponda dependiendo de si su sistema operativo es de 32 o 64 bits y ejecute el archivo ejecutable (.exe) correspondiente.















  5. Crear un DSN a nivel de sistema para la base de datos en SQLite

  6. Presione Inicio -> Ejecutar y luego digite odbcad32 y presione retorno (enter) para el administrador odbc de 64 bits.


    Presione Inicio -> Ejecutar y luego digite C:\Windows\SysWOW64\odbcad32.exe y presione retorno (enter) para el administrador odbc de 32 bits.


    Haga click en la pestaña DSN de Sistema (System DSN).


    Haga click en agregar (add).

    Seleccione el controlador apropiado.


    Enter your SQLite database path. Note that some of the options are documented at the driver site. I suggest leaving them as they are initially. Introduzca la ruta a su base de datos de SQLite. Note que las opciones estan documentadas en sitio web del controlador.


    Notice the 32 bit driver is only editable from a 32 bit administrator and the 64 bit driver is only editable from the 64 bit administrator. Note que el controlador de 32 bits solo es editable desde el administrador de 32 bit, así como el controlador de 64 bits solo es editable desde el administrador de 64 bits.


    Note que los botones remover (remove) y configurar (configure) estan deshabilitados.


  7. Crear el Linked Server en SQL Server

  8. A continuación mostramos el código T-SQL para crear el Linked Server a nuestra base de datos SQLite. Este tipo de Linked Server no necesita cuenta de acceso (Login) ni tampoco ningún contexto de seguridad.

    USE [master]
    GO
    EXEC sp_addlinkedserver
      
    @server = 'Mobile_Phone_DB_64', -- Nombre con el que identificaras el Link Server en SSMS
      
    @srvproduct = '', -- Puede estar en blanco pero no puede ser NULL
      
    @provider = 'MSDASQL',
      
    @datasrc = 'Mobile_Phone_DB_64' -- El nombre de la conexión DSN de Sistema
    GO

  9. Seleccionar los datos de la fuente e insertarlos en nuestra tabla en SQL Server

  10. Ahora haga click sobre el Linked Server y navegue hasta encontrar las tablas, luego simplemente ejecute sus consultas sobre las tablas.

    SELECT *
    FROM OPENQUERY(Mobile_Phone_DB_64 , 'select * from db_notes')
    GO


    Ahora usted puede utilizar una consulta con la clausula INTO para crear tablas con los datos que usted requiera en SQL Server.

    SELECT * INTO SQLite_Data -- Esto crea una tabla nueva con los datos seleccionados
    FROM OPENQUERY(Mobile_Phone_DB_64 , 'select * from db_notes')
    GO


    Luego verifique los tipos de datos en su tabla destino en SQL Server y cambie por los tipos correspondientes.

Referencias

martes, 22 de octubre de 2013

Migración de Accesos (Logins) con Contraseña entre Instancias de SQL Server

Autor: Alfredo Arias

Problema:

En nuestro día a día como DBAs, muchas veces nos encontramos con la necesidad de transferir todas o algunas de las bases de datos de un servidor a otro, ya sea por alguna falla a nivel de hardware o actualización de equipos o por una simple re-estructuración a nivel de infraestructura, sea cual sea el motivo de este cambio trae consigo muchos inconvenientes tanto administrativos como de seguridad, ya que al cambiar de servidor perderemos los logins asociados a nuestros usuarios en las bases de datos debido a que estos se encuentran relacionados a través del Security Identifier (SID) del login el cual se perderá si simplemente creamos otros logins en el servidor/instancia destino, sin contar que también perderemos las contraseñas de todos los accesos, recrear todos los logins, reasignar contraseñas, actualizar los archivos de configuración de todas las aplicaciones que accedían a estas bases de datos generaría una carga administrativa y un tiempo fuera de línea para nuestras aplicaciones, servicios y usuarios demasiado alta y a la vez inaceptable.


Solución:
  1. Conectate a la instancia de origen con SQL Server Management Studio.

  2. Abre una nueva ventana del Query Editor y corre el siguiente script. Este script crea dos procedimientos almacenados en la base de datos master. Estos procedimientos son sp_hexadecimal y sp_help_revlogin.

  3. 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


  4. Ejecute la siguiente instrucción:

  5. EXEC sp_help_revlogin;
    GO

    El resultado de ejecutar el procedimiento almacenado sp_help_revlogin es nuestro script de creación de logins, el cual nos servirá para crear en nuestro servidor/instancia destino los accesos o logins con los Security Identifiers (SID) y contraseñas originales, y por lo tanto nuestros logins y usuarios serán identicos a los de nuestro servidor/instancia original sin necesidad de re-mapear los usuarios a los accesos (logins), ni tampoco tendremos la necesidad de reasignar contraseñas, ni actualizar los archivos de configuración de nuestras aplicaciones.

  6. En el servidor destino, inicia SQL Server Management Studio, y conectate a la instancia de SQL Server destino (a la cual deseamos mover las bases de datos y logins con Security Identifiers (SID) y Contraseñas.

  7. Ejecute el script generado en el paso 3 en el servidor/instancia destino.


Referencias: