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