Home > SQL Server, T-SQL > SQL Server Script user with SID

SQL Server Script user with SID

The Problem

When you move sql logins around servers the logins are created with an SID that uniquely identifies the login.

Assume you setup two logins manually, one on your production server and one on your dev server.

When you restore your prod database to development your Login will be “disconnected” from the restored database and the SID of the “User” in the restored production database does not match the SID of the Login on your dev box.

The Solution

Create your login specifying the SID.
The easiest way is to let SQL set and SID and then read it off the DB and use it in your new login creation script
This script should do it

Use master
GO

-- Step 1 - Create Login
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'usr_website')
DROP LOGIN [usr_website]
GO

CREATE LOGIN [usr_website] WITH PASSWORD = 'password:-)'
GO

-- Step 2 - Get SID
Declare @name nvarchar(254)

set @name = 'usr_website'

SELECT 
	[sid]
	,[status]
	,[name]
	,[dbname]
	,[language]
	,[denylogin]
	,[hasaccess]
	,[isntname]
	,[isntgroup]
	,[isntuser]
	,[sysadmin]
	,[securityadmin]
	,[serveradmin]
	,[setupadmin]
	,[processadmin]
	,[diskadmin]
	,[dbcreator]
	,[bulkadmin]
	,[loginname]
FROM [msdb].[sys].[syslogins]
where name = @name or @name is null

-- Step 3 - Create Login with SID
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'usr_website')
DROP LOGIN [usr_website]
GO

CREATE LOGIN [usr_website] WITH PASSWORD = 'password:-)', SID = 0x346837D1EC9C484C91A3B46D7EC7EABF
GO

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

Categories: SQL Server, T-SQL Tags:
  1. No comments yet.
  1. No trackbacks yet.