Updating Database User SID In SQL 2005

Hi all

We're currently undertaking the development of our first SQL Server 2005 database and we've stumbled across a problem with our method of deployment from Development through System Test, UAT and, finally, to Production.

Under SQL Server 2000 we would build a new database in Development and assign permissions to a specific database user which was linked to an appropriate Windows Login - the whole build process was scripted. We would then hand the mdf and ldf files over to the Operations team who would attach the database to the System Test server, create a new Windows Login (appropriate for the environment), then run a script which would update the SID stored against the database user to the SID of the appropriate login.

e.g.

Database user (consistent through all environments) = AppDAL

Development: Windows Login = DOMAINAppDEVDAL

System Test: Windows Login = DOMAINAppSYSDAL

UAT: Windows Login = DOMAINAppUATDAL

Production: Windows Login = DOMAINAppLIVEDAL



The script, which directly updates the dbo.sysusers table in SQL 2000, links the AppDAL user to the appropriate Windows Login.



Is there any way to perform this task under SQL Server 2005? I'm aware of the sp_change_users_login stored proc, however this only works for SQL Server logins and not Windows logins. Also I'm aware that direct updates to system tables are not allowed under SQL Server 2005 and were not recommended under SQL Server 2000 so we were actually using an unsupported method during deployment. My guess is that we will have to change our method of deployment and rely on the Operations team to build the database in each enviroment from scripts, however I was wondering if there is another way before we go ahead.

Interestingly SQL Server 2005's sp_change_users_login stored proc contains SQL that does not run outside of the stored proc:

EXEC %%UserOrGroup(Name = @UserNamePattern).SetSID(SID = @loginsid, IsExternal = 0, IsGroup = 0)

SQL Server 2005 will not allow creation of a new stored proc that contains this SQL, wrapping the code in EXEC('') causes an error during runtime.

Is anyone familiar with the above syntax? Searching on Google has been fruitless.


Thanks

Chris