I have a SQL Server Database Project (.NET 4.5) in TFS 2012. As a DBA, i'm looking for a mechanism whereby the developers can create 'CREATE USER' sql scripts and assign their permissions within the database to match the application without needing to know what login it eventually maps up to. This is because I want to hide the logins from live and test environments from them and have the DBAs create these logins in each environment as necessary.
If I go into the project and create a new user i.e. [MyApplication] the default script suggests the following template:
CREATE USER [MyApplication]
WITHOUT LOGIN
WITH DEFAULT_SCHEMA = dbo
GO
Which is great, as I can create a user which is mapped up to no login, and then when it's deployed I can run:
ALTER USER [MyApplication] WITH LOGIN=[MyApplicationLogin], Name=[MyApplication]
which is fine as long as the user is to be mapped to a SQL login. If the login is to be mapped out to a windows login, SQL throws an error when we try to remap:
Msg 33016, Level 16, State 1, Line 1
The user cannot be remapped to a login. Remapping can only be done for users that were
mapped to Windows or SQL logins.
So the question is, what is the best way to set this up in the project so that the developers can create a user which can be named whatever they like within the database users and then how would we re-map this to a windows auth login?
thanks in advance!