Sql-server – TFS 2012 – Database Project – User with NO LOGIN map to Windows – best practices

Securitysql serversql-server-2012

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!

Best Answer

The idea of CREATE USER ... WITHOUT LOGIN isn't so you can map it later with ALTER USER ... WITH LOGIN (SQL Server simply refuses, as youhave found).

Instead, the idea is for application developers to change the security context before issuing SQL statements, by first doing EXECUTE AS USER = '<user>' WITHOUT REVERT to shed permissions.

This kind of gets you halfway to where you want to be. You ultimately still need to map some SQL Server logins to some SQL Database Users, but you reduce your need to know in advance what they will be, because you declare access-control permissions against the 'WITHOUT LOGIN' user.

See:

  1. User without login purpose
  2. SQL SERVER – Importance of User Without Login – T-SQL Demo Script
Related Question