I'm trying to publish locally against a SQLExpress instance to test the publish capability of VS Database Project and i"m running into an error where it's trying to create a user that already exists within the database. The user creation isn't being wrapped within an IF EXISTS and I'm not seeing any type of setting to control or enforce this.
Specifically, it's throwing: Creating [xyz\abc46518]... (208,1): SQL72014: .Net SqlClient Data Provider: Msg 15063, Level 16, State 1, Line 1 The login already has an account under a different user name. (208,0): SQL72045: Script execution error. The executed script: CREATE USER [xyz\abc46518] FOR LOGIN [xyz\abc46518];
While other parts of the script have 'IF EXISTS' and 'IF NOT EXISTS', this part of the script does not.
I'd like to be able to have this as a part of the script to control the users within the database. Should someone choose to grant access and it's not in source, it's going away when we deploy.
I started using database projects, they are great, except for the user permissions. I only have dba permissions to our databases, whereas handling logins is at the server level.
So when creating the database projects I'd get the following code generated:
CREATE USER [UserName] for login
Which when I went to build the project would error.
Well I wanted the users, but didn't want the hassle of having to keep track of post deployment scripts, largely because they ruined my lovely TFS structure.
My solution, which is a bit of a hack, instead of creating a user, I just created a role with the same name:
CREATE ROLE [UserName] AUTHORIZATION [dbo];
Now I can assign permissions to the user for my objects (I know all access should be through roles, but its not my database, so I'm happy to hack a fix)
We never deploy roles ourselves, so it doesn't matter to us devs that its a role or a user.