Generate Script for all Non-Cluster Indexes in a database

you can't generate script for all Non-cluster indexes (Script for only Non-cluster indexes) by right on database --> tasks --> Generate Scripts. It will generate scripts including table schema. To generate only non-clusterindexes use below script. 

using  below script you can generate script for all Non Cluster indexes in a database 

declare @EmptyString varchar(1)

select @EmptyString = ''

-- 35 is the lenght of the name field of the master.dbo.spt_values table

declare @IgnoreDuplicateKeys varchar(35),

    @Unique varchar(35),

    @IgnoreDuplicateRows varchar(35),

    @Clustered varchar(35),

    @Hypotethical varchar(35),

    @Statistics varchar(35),

    @PrimaryKey varchar(35),

    @UniqueKey varchar(35),

    @AutoCreate varchar(35),

    @StatsNoRecompute varchar(35)

select @IgnoreDuplicateKeys = name from master.dbo.spt_values 

    where type = 'I' and number = 1 --ignore duplicate keys

select @Unique = name from master.dbo.spt_values 

    where type = 'I' and number = 2 --unique

select @IgnoreDuplicateRows = name from master.dbo.spt_values 

    where type = 'I' and number = 4 --ignore duplicate rows

select @Clustered = name from master.dbo.spt_values 

    where type = 'I' and number = 16 --clustered

select @Hypotethical = name from master.dbo.spt_values 

    where type = 'I' and number = 32 --hypotethical

select @Statistics = name from master.dbo.spt_values 

    where type = 'I' and number = 64 --statistics

select @PrimaryKey = name from master.dbo.spt_values 

    where type = 'I' and number = 2048 --primary key

select @UniqueKey = name from master.dbo.spt_values 

    where type = 'I' and number = 4096 --unique key

select @AutoCreate = name from master.dbo.spt_values 

    where type = 'I' and number = 8388608 --auto create

select @StatsNoRecompute = name from master.dbo.spt_values 

    where type = 'I' and number = 16777216 --stats no recompute

select 'CREATE NONCLUSTERED INDEX ['+i.name+'] ON [dbo].['+o.name+'] ('+

  case when index_col(o.name,indid, 1) IS NULL then '' else '[' + index_col(o.name,indid, 1) + '] ASC'end +''+

  case when index_col(o.name,indid, 2)IS NULL then ''  else +',['+index_col(o.name,indid, 2)+ '] ASC'end+''+

  case when index_col(o.name,indid, 3)IS NULL then '' else +',['+index_col(o.name,indid, 3)+ '] ASC'end+''+

  case when index_col(o.name,indid, 4)IS NULL then '' else +',['+index_col(o.name,indid, 4)+ '] ASC'end+''+

  case when index_col(o.name,indid, 5)IS NULL then '' else +',['+index_col(o.name,indid, 5)+ '] ASC'end+''+

  case when index_col(o.name,indid, 6)IS NULL then '' else +',['+index_col(o.name,indid, 6)+ '] ASC'end+''+

  case when index_col(o.name,indid, 7)IS NULL then '' else +',['+index_col(o.name,indid, 7)+ '] ASC'end+''+

  case when index_col(o.name,indid, 8)IS NULL then '' else +',['+index_col(o.name,indid, 8)+ '] ASC'end+''+

  case when index_col(o.name,indid, 9)IS NULL then '' else +',['+index_col(o.name,indid, 9)+ '] ASC'end+''+

  case when index_col(o.name,indid, 10)IS NULL then '' else +',['+index_col(o.name,indid, 10)+ '] ASC'end+''+

  case when index_col(o.name,indid, 11)IS NULL then '' else +',['+index_col(o.name,indid, 11)+ '] ASC'end+''+

  case when index_col(o.name,indid, 12)IS NULL then '' else +',['+index_col(o.name,indid, 12)+ '] ASC'end+''+

  case when index_col(o.name,indid, 13)IS NULL then '' else +',['+index_col(o.name,indid, 13)+ '] ASC'end+''+

  case when index_col(o.name,indid, 14)IS NULL then '' else +',['+index_col(o.name,indid, 14)+ '] ASC'end+''+

  case when index_col(o.name,indid, 15)IS NULL then '' else +',['+index_col(o.name,indid, 15)+ '] ASC'end+''+

  case when index_col(o.name,indid, 16)IS NULL then '' else +',['+index_col(o.name,indid, 16)+ '] ASC'end+''+') ON [Primary]'  

from sysindexes i, sysobjects o

where i.id = o.id and

      indid >1 and indid < 255 --all the clustered (=1), non clusterd (>1 and <251), and text or image (=255) 

      and o.type = 'U' --user table

      --ignore the indexes for the autostat

      and (i.status & 64) = 0 --index with duplicates

      and (i.status & 8388608) = 0 --auto created index

      and (i.status & 16777216)= 0 --stats no recompute

      order by o.name

Install SQL Server 2012 in Stand Alone Machine

Requirements for Installing SQL Server 2012:

1.       Operating system Requirement :

You can install in below operating systems

Windows Server 2012,

Windows 8,

Windows server 2008 R2 SP1,

Windows 7 SP1.

You can also install in below operating systems but you require to install update http://support2.microsoft.com/?kbid=956250 on below operating system before initiating the installation.

Windows Vista SP2

Windows Server 2008 SP2

2.       NET Framework

.NET 4.0 is a requirement for SQL Server 2012. SQL Server installs .NET 4.0 during the feature installation step.

You must enable or install.NET 3.5 SP1, If you select to install Database Engine, Reporting Services, Master Data Services, Data Quality Services, Replication, SQL Server Management Studio on Windows Vista SP2 or Windows Server 2008 SP2 operating systems. OS wont have .NET 3.5 SP1 Framework 

3.       Windows Powershell

Powershell 2.0 is mandatory to install SQL Server 2012. If  SQL Server setup reports Powershell 2.0 is not present, you can install or enable it.

4.       Internet Explorer 7 or a later version.

5.       Hard Disk : SQL Server 2012 requires minimum of 6 GB.

6.       Memory

 Minimum :  

<![if !supportLists]>1.       <![endif]>Express : 512 MB

<![if !supportLists]>2.       <![endif]>Other  : 1 GB

       Recommended

<![if !supportLists]>                                                         i.            <![endif]>Express : 1 GB

<![if !supportLists]>                                                       ii.            <![endif]>Other  : 4 GB

Installation Steps:

Go to setup file location  click on setup.exe

Next you will see the SQL Server Installation Center  page. In Left side pane select Installation Tab

In installation page, right side pane you will found 4 option 

               

Option 1 : New SQL Server stand-alone installation or add features to existing installation 

                                Using this option you can install Stand-alone server  or add SQL Server features to an existing installation

Option 2 : New SQL Server failover cluster installation

                                Using this option you can install a SQL Server in Failover Cluster

Option 3 : Add Node to the SQL Server failover cluster

                                This option is used to add a node to an existing failover cluster

Option 4 : Upgrade from SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2

                                Using this option we can upgrade the existing SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2 to SQL Server 2012

Now we are performing SQL Server stand-alone installation so click on Option 1.

It will initiate the stand-alone installation process of SQL Server 2012

                               

Next screen displays you Setup Support Rules screen, click on  button to see all the rules information.

Click on Ok

Above screen will provide you the available latest updates.

Click on the Next.

In next screen you need to select weather is it new installation are adding features to an existing installed instance.

Select Perform a new installation of SQL Server 2012 radio button for fresh installation

In next screen need to select weather is it free edition or need to provide product key and click on Next

Next  is the license  terms screen. You need to accept the license terms and click on Next.

l

Select All features with defaults to install all features with default values and select Next

Select the what ever you want install in the instances. If this is the first installation you can select what ever shared features you want. If it is another instance already installed shared features are grayed out. Click on Next

Difference between DateTime and DateTime2 DataType

DATETIME2 is the new data type introduced in SQL Server 2008

DATETIME2 provides more seconds precision & has larger date range, a larger default fractional precision. DATETIME is limited to 3 1/3 milliseconds, while DATETIME2 can be accurate down to 100ns

Advantages of DATETIME2

DATETIME2 has a date range of "0001 / 01 / 01" through "9999 / 12 / 31" while the DATETIME type only supports year 1753-9999

Larger range of values

Better Accuracy

Smaller storage space (if optional user-specified precision is specified)

Precision, scale

                                                                                                        0 to 7 digits, with an accuracy of 100nanoseconds.

                                                                                                        The default precision is 7 digits.

Storage Size

                                                                                                        6 bytes for precision less than 3

                                                                                                        7 bytes for precision 3 and 4.

All other precision require 8 bytes.

DATETIME2 with fractional seconds precision of 3 is same as DATETIME data type. And DATETIME2 (precision of 3) uses 7 bytes of storage instead of 8 byte

If you only need the date without time part use DATE

 If you only need the time without date part use TIME

 Using DATE & TIME data types instead of DATETIME2 in above situations you save the space

SQL Server Job history

Using below script you can fetch the specific job history like executing date and durations

select job_name, run_datetime, run_duration

from

(

    select job_name, run_datetime,

        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +

        SUBSTRING(run_duration, 5, 2) AS run_duration

    from

    (

        select DISTINCT

            j.name as job_name, 

            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  

                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,

            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)

        from msdb..sysjobhistory h

        inner join msdb..sysjobs j

        on h.job_id = j.job_id

    ) t

) t

where job_name = 'Job name '

order by job_name, run_datetime

--- Query results are like below

SQL Script to Change the database to read only and read write

Below script is used to change the database into single user mode. To kill the current connections change the database to single user mode then change it to single user mode.Replace the database name from MyDATABASE to the database name need to change to READ ONLY

USE [master]
GO
ALTER DATABASE [MyDATABASE]  SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
 ALTER DATABASE [MyDATABASE] SET READ_ONLY WITH NO_WAIT
GO
ALTER DATABASE [MyDATABASE] SET MULTI_USER
GO

Below script is used to change the database into read/write user mode.

 ALTER DATABASE [MyDATABASE] SET READ_WRITE WITH NO_WAIT

Rename the sql server database

There are couple of options are available to rename the database.
Option 1 :
 Rename the "MyDATABASE" to "MyDATABASE_NEW" using sp_renamedb command

 EXEC sp_renamedb 'MyDATABASE', 'MyDATABASE_NEW' 

Option 2:
 Rename the "MyDATABASE" to "MyDATABASE_NEW" using ALTER DATABASE Commnad

USE [master]
GO

ALTER DATABASE [MyDATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [MyDATABASE] MODIFY NAME = MyDATABASE_NEW
GO
ALTER DATABASE [MyDATABASE_NEW] SET MULTI_USER
GO

SQL Server installation methods

There are three types of Installation methods 

1. Local/Standard installation

2. Unattended installation 

3. Remote Installation 

Local/Standard installation

To perform standard installation you need to login into the machine and the login is part of local administrator group. 

Unattended installation 

It is also known as silent installation. Install multiple SQL Servers with identical configurations without using the interactive SQL Server setup is known as Unattended installation. 

Installation initiates from command prompt ( batch file will call setup file).

All the parameters are saved in configuration file (setup.exe) 

An unattended installation using a configuration file using the following command line at a new command prompt.

D:\SQLFULL_x86_ENU\Setup.exe /ConfigurationFile=D:\SQLFULL_x86_ENU\SQLConfigurationFile.ini

 

Remote Installation

You can install SQL Server from remote machine also. 

To perform remote installation the user account should have following permissions 

1. Have administrative rights on the remote computer. 

2. Have read access to the setup source files folder.

TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.

2015-02-17 12:27:38.19 spid15s     Error: 26014, Severity: 16, State: 1.

2015-02-17 12:27:38.19 spid15s     Unable to load user-specified certificate [Cert Hash(sha1) "5B4FF9FFF4E6752A3AD51489E1A9C455E580BCC3"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.

2015-02-17 12:27:38.19 spid15s     Error: 17182, Severity: 16, State: 1.

2015-02-17 12:27:38.19 spid15s     TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property. 

2015-02-17 12:27:38.19 spid15s     Error: 17182, Severity: 16, State: 1.

2015-02-17 12:27:38.19 spid15s     TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property. 

2015-02-17 12:27:38.19 spid15s     Error: 17826, Severity: 18, State: 3.

2015-02-17 12:27:38.19 spid15s     Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

2015-02-17 12:27:38.19 spid15s     Error: 17120, Severity: 16, State: 1.

2015-02-17 12:27:38.19 spid15s     SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Event ID 17182: TDSSNIClient initialization failed with error 0x7e, status code 0x3a.

Event ID 17826: Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

Event ID 17120: SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Solution :

1. Check 'Forced Encryption' (Turned False) and 'Certificate' value is cleared under SQL Server Configuration Manager.
2> Check the following registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer\SuperSocketNetLib\Certificate
and clear the Certificate values.

Shrink Database Log file in SQL Server

 

Method 1 : This script will shrink the all log files of specific database by  change the recovery modle to simple  and again it rever back the recovery model

 

DECLARE @tbl TABLE

(

      ID INT IDENTITY(1,1),

      DBNAME NVARCHAR(1000),

      [FileName] NVARCHAR(1000)

)

  

INSERT INTO @tbl

SELECT DB_NAME(DbID),st.NAme from sys.sysaltfiles  ST INNER JOIN Sys.databases SB on  ST.dbid = sb.database_id where sb.name = 'TEST2008'  AND FileName LIKE '%ldf' and sb.state = 0

 

DECLARE @MinID INT , @MaxID INT,@DBName NVARCHAR(1000),@FileName NVARCHAR(1000),@RecoveryModel NVARCHAR(1000),@SQL NVARCHAR(MAX)

 

SELECT @MinID = MIN(ID),@MaxID = MAX(ID) FROM @tbl

 

 

WHILE(@MinID <=@MaxID)

BEGIN

      SELECT @DBName = DBNAME,@FileName=[FileName] FROM @tbl where ID = @MinID

 

      SELECT      @RecoveryModel = recovery_model_desc FROM sys.databases where name = @DBName

 

            SELECT      @SQL= N'USE ['+ @DBName+']'+CHAR(10)+CHAR(10)

                  +CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY SIMPLE WITH NO_WAIT'ELSE N''END+CHAR(10)+

                  +N'DBCC SHRINKFILE('+@FileName+',1)'+CHAR(10)

                  +CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY '+@RecoveryModel+N' WITH NO_WAIT'+CHAR(10) ELSE N'' END

                  +CHAR(10)

      PRINT @SQL

      EXEC SP_EXECUTESQL @SQL

     

      SELECT @MinID = @MinID +1

END

 

Method 2: This script will shrink the all databases log files, depends on the size of log file by  change the recovery modle to simple  and again it rever back the recovery model

 

DECLARE @tbl TABLE

(

      ID INT IDENTITY(1,1),

      DBNAME NVARCHAR(1000),

      [FileName] NVARCHAR(1000)

)

INSERT INTO @tbl

SELECT DB_NAME(DbID),st.NAme from sys.sysaltfiles  ST INNER JOIN Sys.databases SB on  ST.dbid = sb.database_id where Size > 100000 AND FileName LIKE '%ldf' and sb.state = 0

 

DECLARE @MinID INT , @MaxID INT,@DBName NVARCHAR(1000),@FileName NVARCHAR(1000),@RecoveryModel NVARCHAR(1000),@SQL NVARCHAR(MAX)

 

SELECT @MinID = MIN(ID),@MaxID = MAX(ID) FROM @tbl

 

 

WHILE(@MinID <=@MaxID)

BEGIN

      SELECT @DBName = DBNAME,@FileName=[FileName] FROM @tbl where ID = @MinID

 

      SELECT      @RecoveryModel = recovery_model_desc FROM sys.databases where name = @DBName

 

            SELECT      @SQL= N'USE ['+ @DBName+']'+CHAR(10)+CHAR(10)

                  +CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY SIMPLE WITH NO_WAIT'ELSE N''END+CHAR(10)+

                  +N'DBCC SHRINKFILE('+@FileName+',1)'+CHAR(10)

                  +CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY '+@RecoveryModel+N' WITH NO_WAIT'+CHAR(10) ELSE N'' END

                  +CHAR(10)

      PRINT @SQL

      EXEC SP_EXECUTESQL @SQL

     

      SELECT @MinID = @MinID +1

END

 

Kill all user SPIDs in a SQL Server Database

Using this script you can kill all the SPID connected to TEST2008 database 

 

 

 

DECLARE @DB SYSNAME 

SET @DB = 'Test2008'  -- Here you need to give the database name where you need to kill all connection

 

 

DECLARE @SPID VARCHAR(4), @cmdSQL VARCHAR(10)

 

IF OBJECT_ID('tempdb..#KilledSpidsInfo ') IS NOT NULL

BEGIN

DROP TABLE  #KilledSpidsInfo

END

 

SELECT * into #KilledSpidsInfo FROM master.dbo.sysprocesses

WHERE SPID > 50 

AND SPID != @@SPID 

AND DBID = DB_ID(@DB) 

 

select * from #KilledSpidsInfo

 

DECLARE cCursor CURSOR

FOR SELECT CAST(SPID AS VARCHAR(4)) FROM master.dbo.sysprocesses

WHERE SPID > 50 

AND SPID != @@SPID 

AND DBID = DB_ID(@DB) 

 

OPEN cCursor

 

FETCH NEXT FROM cCursor INTO @SPID

 

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmdSQL = 'KILL ' + @SPID

 

 EXEC (@cmdSQL)

FETCH NEXT FROM cCursor INTO @SPID

END

 

CLOSE cCURSOR

 

DEALLOCATE cCURSOR

 

change server level collation for a SQL Server Instance

Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales

The server collation acts as the default collation for all system databases that are installed with the instance of SQL Server, and also any newly created user databases. The server collation is specified during SQL Server installation.  It is not mandatory that we change the default server level collation, because you can specify a different collation level when you create users databases, but you need to remember to specify this when creating user databases.

To change the default SQL Server collation you can simply rebuild the system databases. When you rebuild the master, the model, msdb and tempdb system database are actually dropped and recreated in their original location. If a new collation is specified in the rebuild statement the system databases are rebuilt using that collation setting. Any user modifications to these databases will be lost, so it is important to backup any of this information you wish to retain

Step 1

First check the existing SQL Server collation setting of your instance. Run the command below to get the collation value of your SQL Server instance.

SELECT SERVERPROPERTY('Collation')

Step 2

Make sure to record all server level settings before rebuilding the system databases to ensure that you can restore the system databases to their current settings.  

Step 3

Take backup of all user and server databases.

Take the backup of all jobs, maintenance plans, logins and their access levels

To take all jobs backup in OBJECT EXPLORER window select job folder & in right side OBJECT EXPLORE DETAIL window it displays all jobs, select all jobs and right click on your selection then choose the SCRIPT AS option to create the script for all jobs.

Next is to secure your logins, passwords and their access levels. You can use sp_help_revlogin stored procedure to create a script for all logins so they can be recreated easily. And take server level permissions using script

Step 4

Detach all user databases before rebuilding your system databases. If you leave databases attached they will be detached and will be found in the database folder.

Step 5

Now its time to rebuild your system databases. This operation will recreate your master database and all existing settings will be reset. Run the below command from a Windows command prompt. Make sure to run this command from the directory where you have placed your SQL Server setup files. Once you press enter, a separate window will appear to show you the progress bar. Once the rebuild is done, that window will disappear.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MANVENDRA /SQLSYSADMINACCOUNTS=domain\adminUser /SAPWD= SaPWD@SqlRebuild /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

* SQL_Latin1_General_CP1_CI_AI –this is the new collation need to change

Once the rebuild operation is complete, check the server collation to verify whether this change is successful or not. As we can see in the screenshot below, the server collation has changed to SQL_Latin1_General_CP1_CI_AI. At this point we cannot restore any of the system databases, because doing so will revert back to the previous collation setting. So we will need to use the scripts that were created to recreate logins, jobs

Step 6

Attach all user databases which were detached in Step 4.

Step 7

Now change the collation settings of all user databases. It's not necessary to change the collation settings for the user databases, it totally depends on your requirement.

Run the commands below to change the collation settings of your user databases.

ALTER DATABASE CollationChangeDBName collate SQL_Latin1_General_CP1_CI_AI

Sometimes the command fails to execute and you get this error:

Msg 5075, Level 16, State 1, Line 1 The object 'PK_xxxx' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

In that case you may need to export all data and recreate the database with the new collation settings.

Step 8

Now run all of the scripts which were created in Step 3 to restore jobs, alerts, logins, operators, etc...  Also don't forget to change the server level configuration settings which were captured in Step 2.

Now your instance is ready to use the new server level collation.

Transfer logins and passwords between instances of SQL Server(revlogin)

To create a log in script that has a blank password, follow these steps:

On server A, start SQL Server Management Studio, and then connect to the instance of SQL Server from which you moved the database.

Open a new Query Editor window, and then run the following 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

Note This script creates two stored procedures in the master database. The procedures are named sp_hexadecimal and sp_help_revlogin.

Run the following statement:

EXEC sp_help_revlogin

The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

On server B, start SQL Server Management Studio, and then connect to the instance of SQL Server to which you moved the database.

Important Before you go to step 5, review the information in the "Remarks" section.

Open a new Query Editor window, and then run the output script that is generated in step 3.

Thanks

Venkat Sangu