Monday 27 June 2011

Howto remap the Windows Account to a SQL Server login



/*
Remaps the Windows Account to a database user. Works with SQL Server 2005, 2008, 2008 R2.

Expects the following variables to come from sqlcmd.exe:
$(DbName) - database name, i.e. AdventureWorks
$(ServerLogin) - the login to use, should be Windows Account, either local or domain. DON'T USE localhost! USE computer name or domain name!!
$(DbUserName) - the user within the database

Usage:
SQLCMD -S"MyServer" -E -i"RemapLogin.sql" -v DbName="AdventureWorks" -v ServerLogin="%COMPUTERNAME%\SomeUser" -v DbUserName="some_user_in_db" -b
*/

USE master
GO

IF EXISTS (SELECT sid FROM master..syslogins WHERE [name]=N'$(ServerLogin)')
BEGIN
PRINT 'Dropping the $(ServerLogin) login';
DROP LOGIN [$(ServerLogin)];
END
GO

PRINT 'Creating the $(ServerLogin) login from Windows Account';
GO
CREATE LOGIN [$(ServerLogin)] FROM WINDOWS
GO
-- Grant server role bulkadmin that's required for BCP.
EXEC master..sp_addsrvrolemember [$(ServerLogin)], 'bulkadmin'
GO
USE [$(DbName)]
GO
PRINT 'Mapping the $(DbUserName) user in DB to newly created login';
GO

/*
Need to detect SQL Server version because LOGIN clause was added only in SQL 2005 SP2
*/
IF EXISTS (SELECT ProductVersion FROM
(SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion, SERVERPROPERTY('ProductLevel') AS ProductLevel) Vrsn
WHERE CAST(ProductVersion AS varchar(100)) LIKE '9.00.%' AND CAST(ProductLevel AS varchar(100)) IN ('RTM','SP1'))
BEGIN
PRINT 'SQL Server 2005 pre-SP2 detected';
ALTER USER $(DbUserName) WITH NAME = $(DbUserName);
END
ELSE
BEGIN
PRINT 'SQL Server 2005 SP2 or later or 2008 detected';
EXEC sp_executesql N'ALTER USER $(DbUserName) WITH NAME = $(DbUserName), LOGIN = [$(ServerLogin)]';
END
GO

PRINT 'FINISHED!';
GO

No comments:

Post a Comment