Transfer logins in SQL Server 2005 and SQL Server 2008

/*------------------------------------------------------------------------------------------------------------

Transfer logins in SQL Server 2005 and SQL Server 2008

In this script we cover sp_help_revlogin stored procedure which can help you copy sql server logins from one server to another server running SQL Server 2008.

This can be useful when you are trying to copy a database from one server (maybe production) to another (development or stage). Remember when you copy a database it does not move the SQL Server Logins which are present at the server level. You have to manually transfer the login accounts from the source ms sql server to target ms sql server.

Using the information here you can easily do this task.

------------------------------------------------------------------------------------------------------------*/

/*

Transfer logins SQL Server 2005 - Step 1

 Create sp_help_revlogin procedure

Sources:

http://support.microsoft.com/kb/246133/

http://blog.netnerds.net/2009/01/migratetransfer-sql-server-2008200520007-logins-to-sql-server-2008/

This needs to be run on the source sql server, the one you are copying the logins from


*/

----- Begin Script, Create sp_help_revlogin procedure -----



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


 ----- End Script, Create sp_help_revlogin procedure -----


/*

Transfer logins in SQL Server 2005 - Step 2

Run the following script on the source SQL Server. After you execute the SQL, this stored procedure will go ahead and generate the SQL code for your Logins.  You can then copy this SQL code and execute it on the target server.

*/


--USE [Enter your database]

EXEC master..sp_help_revlogin



For our case I am using Northwind sample database. I have included a screen shot of what this looks like on my machine.

Transfer logins SQL Server 2005 - Step 3

--Execute the sql code generated by the previous step on your target (destination) SQL Server, This will copy all the login information to the destination server.


Here is part of the SQL script.


/* sp_help_revlogin script
** Generated Jun 30 2010  4:08PM on KASHMONEY-PC */


-- Login: ##MS_PolicyEventProcessingLogin##
CREATE LOGIN [##MS_PolicyEventProcessingLogin##] WITH PASSWORD = 0x01003869D680ADF63DB291C6737F1EFB8E4A481B02284215913F HASHED, SID = 0x0A6983CDF023464B9E86E4EEAB92C5DA, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; ALTER LOGIN [##MS_PolicyEventProcessingLogin##] DISABLE

-- Login: ##MS_PolicyTsqlExecutionLogin##
CREATE LOGIN [##MS_PolicyTsqlExecutionLogin##] WITH PASSWORD = 0x01008D22A249DF5EF3B79ED321563A1DCCDC9CFC5FF954DD2D0F HASHED, SID = 0x8F651FE8547A4644A0C06CA83723A876, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; ALTER LOGIN [##MS_PolicyTsqlExecutionLogin##] DISABLE

-- Login: NT AUTHORITY\SYSTEM
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE = [master]

-- Login: NT SERVICE\MSSQLSERVER
CREATE LOGIN [NT SERVICE\MSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE = [master]


Also we have included a screen shot right below of what it looks like:

TAGS: SQL Server 2005, Copy SQL Logins, sp_help_revlogin, SQL Server 2005

Why Table Variables are better than Temporary Tables

If you are working with SQL Server tables that have millions of rows, one common technique to speed up your SQL queries and stored procedures is to either use temporary tables or table variables. There is quite a bit of debate on which is the better option, Table Variables or Temporary Tables. We will take a closer look at the two objects with SQL Profiler to decide the winner.

Temporary tables

Temporary tables are similar to static tables, however they are not permanent database objects. Temporary tables are extremely useful as work tables for storing intermediate results and complex queries.  In addition, SQL server creates and maintains them in Tempdb database and drops them when they are no longer needed. You can virtually do everything with a temporary table that you can do with a standard SQL Server table.  You can create indexes, create defaults, modify the table and basically use the temporary table anywhere you need to use a regular persistent database table.  Temporary tables can also be used in stored procedures to improve performance tuning and query optimization.  There are two types of temporary tables:

Local Temporary table:

This is by far the most common type of a temporary table.  When using this one, the scope of the temporary table is limited to the database connection that creates the temp table. The name of the local temporary table must start with a # (Hash symbol) e.g. #local_temp_table. The local temporary table can either be dropped explicitly (best practice), or when the database connection closes.  If you are using a store procedure, the local temporary table will be dropped when the stored procedure ends execution.

Global Temporary table:

Temporary tables can also be shared across many connections in SQL Server. This is possible by using a global temporary table which is visible to any and all connection in SQL server. This table is only deleted when the last connection explicit drops the temporary tables. Global temporary table must begin with 2 ## signs, e.g.##global_temp_table.
Let us take a look at some of these examples next, we are going to be using Northwind database which is a sample database for SQL server. If you would like to download this database, please visit the Northwind download page.

--Create Table #CUSTOMER_DATA 

CREATE TABLE #CUSTOMER_DATA
( [CONTACTNAME] [NVARCHAR](30) NULL,
[ADDRESS] [NVARCHAR](60) NULL,
[CITY] [NVARCHAR](15) NULL,
[COUNTRY] [NVARCHAR](15) NULL,
)

We will compare the performance of temporary table with a table variable using SQL Server 2008 at the end of this article

Advantages of using temporary tables

Here are some of the advantages of using temporary tables:

-It is possible to create an index on a temporary table
-You can create constraints on a temporary table including Primary Key, unique, NULL and check constraints
-The scope of a temporary table is not only limited to the current session, instead you can extend it to all connections using global temporary table
-Using Statistics is possible on a temporary table
-Temporary table are best suited for big datasets which involves large amount of processing

Disadvantages of using temporary tables

Some of the disadvantages of using temporary tables are:

-For smaller data sets, temporary table are outperformed by table variables
-Generally there is more overhead with temporary table as you have to create the object, populate temporary table  and also log read/write operations
-When using temporary table in a stored procedure, there may be incidence of recompilation

Performance Testing between Temporary Tables and Table Variables

Using Northwind sample database, we are going to create a test table called CUSTOMER_DATA_BAK which contains repeated data from CUSTOMER_DATA table. This test table has 2 million rows so we can compare temporary table with table variable for a large data set. When we run these sql queries we will run a trace in SQL Profiler to capture different parameters and statistics.  We will repeat the test for these scenarios:

1. Temporary table without an index
2. Temporary table with an index
3. Table Variable

Here we have included a transact sql script for testing the first case. It does the following things:

-Clear the data and procedure cache to get a clean baseline
-Create the temporary table
-Populate the temporary table
-Run a select query from the temporary table with formatted customer mailing list


USE Northwind

--TEMP TABLE WITH NO INDEX
--The first command empties the data cache and the second one empties the procedure cache

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

--This displays disk activity and time of TSQL execution

SET STATISTICS IO ON
SET STATISTICS TIME ON

--Create Table #CUSTOMER_DATA

CREATE TABLE #CUSTOMER_DATA
( [CONTACTNAME] [NVARCHAR](30) NULL,
[ADDRESS] [NVARCHAR](60) NULL,
[CITY] [NVARCHAR](15) NULL,
[COUNTRY] [NVARCHAR](15) NULL,
)

--Populate Temp Table #CUSTOMER_DATA

INSERT INTO #CUSTOMER_DATA
SELECT
CONTACTNAME, ADDRESS, CITY, COUNTRY
FROM DBO.CUSTOMER_DATA_BAK
WHERE CITY IN ('SAN FRANCISCO', 'SEATLLE', 'ELGIN', 'LONDON')

--Select formatted data from Temp Table #CUSTOMER_DATA

SELECT
CONTACTNAME + CHAR(13) + CHAR(10) +
ADDRESS + CHAR(13) + CHAR(10) +
CITY + ' ' + COUNTRY + CHAR(13) + CHAR(10) +
CHAR(13) + CHAR(10)AS PRINT_ADDRESS
FROM #CUSTOMER_DATA
WHERE CITY ='LONDON'

--Explicitly drop the Temp Table #CUSTOMER_DATA

DROP TABLE #CUSTOMER_DATA
 
/* SQL PROFILER RESULTS

CPU= 3274
READS= 72194
WRITES= 2297
DURATION= 162519

*/

You can download this sql script on temp table from this location:
When we look at the trace output from the SQL Profiler on temp table, the numbers are as follows:

CPU=3274
READS=72194
WRITES=2297
DURATION=162519

Here is a screen shot from SQL server Profiler trace

http://www.sqlserver2008tutorial.com/blog/temporary-tables/temp-table-profiler.jpg

Performance Testing on Temp Table with an Index

Next we are going to test the same process, however this time we are going to create an index on the temporary table in addition to the existing code. You can download this sql script on temp table with index from this location:

We have included a partial screen shot of this transact sql code when we tested this in Management Studion in SQL Server 2008:

http://www.sqlserver2008tutorial.com/blog/temporary-tables/create-temp-table-index.jpg

When we look at the trace on temp table with an index from the SQL Profiler, here are the numbers:

CPU=6114
READS=757553
WRITES=3035
DURATION=66602

Here is a screen shot from SQL server Profiler trace

http://www.sqlserver2008tutorial.com/blog/temporary-tables/temp-table-index-profiler.jpg

Table variables:

Table variable is simply a data variable of type table. As such Table variables is created using a declare statement.  It offers an alternative approach to using temporary table and is typically faster and efficient for smaller record sets.  A table variable can store results for later processing similar in concept to using a temporary table. Table variable act like a local variables and their scope ends with the batch that is currently using them.

Here is an example of sql code for a Table variable

--Declare Table Variable @CUSTOMER_DATA
DECLARE @CUSTOMER_DATA TABLE

( [CONTACTNAME] [NVARCHAR](30) NULL,
[ADDRESS] [NVARCHAR](60) NULL,
[CITY] [NVARCHAR](15) NULL,
[COUNTRY] [NVARCHAR](15) NULL
)

Advantages of a Table variable

Here are some of the advantages of using table variables:

-In general for smaller record set, table variables outperform the temporary tables
-When using a function with temporary storage, you have to use the table variable as temporary tables will not work
-Table variables do not cause recompilation issues like temporary tables do
-Table variables require fewer system resources causing than them to be less of a performance hit compare to temp tables

Disadvantages of a table variable

Here are some of disadvantages of using table variables:

-The table definition cannot be changed once table variable has been declared
-You cannot use Select Into statements with table variables
-The only way you can create an index in a table variable is to use a Primary Key constraint at the time of table variable declaration
-New Indexes cannot be created after declaration of the table variable
-Performance is slow with table variables when working with large data sets
-Rollback Tran and Truncate table are not allowed with table variables

Performance Testing on Table Variable

Next we are going to repeat the process this time with a table variable.  Here is the script than we are going to use:

USE Northwind

--SQL TABLE VARIABLE
--The first command empties the data cache and the second one empties the procedure cache
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

--This displays disk activity and time of TSQL execution
SET STATISTICS IO ON
SET STATISTICS TIME ON

--Declare Table Variable @CUSTOMER_DATA
DECLARE @CUSTOMER_DATA TABLE
( [CONTACTNAME] [NVARCHAR](30) NULL,
[ADDRESS] [NVARCHAR](60) NULL,
[CITY] [NVARCHAR](15) NULL,
[COUNTRY] [NVARCHAR](15) NULL
)

--Insert data into Table Variable @CUSTOMER_DATA
INSERT INTO @CUSTOMER_DATA
SELECT
CONTACTNAME, ADDRESS, CITY, COUNTRY
FROM DBO.CUSTOMER_DATA_BAK
WHERE CITY IN ('SAN FRANCISCO', 'SEATLLE', 'ELGIN', 'LONDON')

--Select data from Table Variable @CUSTOMER_DATA
SELECT
CONTACTNAME + CHAR(13) + CHAR(10) +
ADDRESS + CHAR(13) + CHAR(10) +
CITY + ' ' + COUNTRY + CHAR(13) + CHAR(10) +
CHAR(13) + CHAR(10)AS PRINT_ADDRESS
FROM @CUSTOMER_DATA
WHERE CITY ='LONDON'

/* SQL PROFILER RESULTS

CPU= 2730
READS= 71000
WRITES= 2289
DURATION= 46184

*/

Next we are going to repeat the process this time with a table variable.  Here is the script than we are going to

You can also download the temp variable sql script from our site
We have included a screen shot of the SQL profiler results on table variables

http://www.sqlserver2008tutorial.com/blog/temporary-tables/table-variable-profiler.jpg

When we look at the trace on temp table from the SQL Profiler output, here are the numbers:

CPU=2730
READS=71000
WRITES=2289
DURATION=46184

Performance Testing Results between Temp Table and Table Variable

Parameter	Temp Table (No    Index)	Temp Table (With Index)	Table Variable
CPU	3274	6114	2730
DURATION	162519	66602	46184

As you can see using Table Variable is a much better option as it used less CPU and took only 1/3 of time to process the same data!!

For futher query optimization tricks and tips, please visit our site on SQL Server Tutorials

Related Links on the Topic

-Temporary Tables vs. Table Variables
-Should I use a #temp table or a @table variable?

TAGS: Table Variables, Temporary Tables