Archive

Archive for September, 2015

SQL Server Script user with SID

September 18th, 2015 No comments

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:

T-SQL Drop temp table if it exists

September 9th, 2015 No comments

The Problem

I often work in SQL Server with temp tables and they can be a pain when developing as you may have code like

Create Table #tblTemp( Id int , Value nvarchar(32))

Insert Into #tblTemp
select Id, Value from SomeOtherTableAndLotsOfJoins

--Drop Table #tblTemp

You comment out the drop as you want to check the results and do some testing but next time you want to run the script you have to remember to call the “Drop Table”

The Solution

Put this at the top of your script

if exists(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tblTemp'))
begin
	drop table #tblTemp;
end
VN:F [1.9.22_1171]
Rating: 5.0/10 (1 vote cast)

Categories: T-SQL Tags: