CREATE USER should have IF NOT EXISTS

ChrisGStevens

The following code is generated when doing my compare and will generate an error since my user does exist.

-- User

IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'MyDomain\svc_devUserName')
CREATE LOGIN [MyDomain\svc_devUserName] FROM WINDOWS
GO
CREATE USER [Svc_UserName] FOR LOGIN [MyDomain\svc_devUserName] WITH DEFAULT_SCHEMA=[UserSchema]
GO

We have a standard USER but the logins change per environment.
This avoids having every environment user change in the roles. The same DatabaseUser is in the Role in all Environments.


Shouldn't the CREATE USER have a IF NOT EXISTS as well?

	
-- User
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'MyDomain\svc_devUserName')
CREATE LOGIN [MyDomain\svc_devUserName] FROM WINDOWS
GO
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = N'Svc_UserName')
CREATE USER [Svc_UserName] FOR LOGIN [MyDomain\svc_devUserName] WITH DEFAULT_SCHEMA=[UserSchema]
GO

sam.blackburn

I suspect the reason `IF NOT EXISTS ... CREATE USER` was never implemented in Compare is that there often seem to be differences in setup between different environments, e.g. the dev/test instance isn't on the same domain as production. You might be better off putting the appropriate users in place by hand and using the Ignore Users option, to be honest.

Just noticed there is a UserVoice page for existence checks on users, although it only has 5 votes at the moment.

Jessica R

Hi @ChrisGStevens!

Can I first check what version of SQL Compare you're using (Help>About)?

Can you please send the create script for the user on both sides so that I can reproduce this locally?

Thanks!

ChrisGStevens

Right now I am using 13.0.2.5109.
Taking the below script and doing the compare I am actually ending up with a change script does have any changes in it now; goes from BEGIN TRANSACTION and then the next batch is COMMIT TRANSACTION.
Reviewing the differences in SQL Compare I can see that the LOGINs are different because of the qa vs dev and the Database User is the same.

This is actually a little bit different than the original issue I had and trying to figure out what state I was in.
Hopefully not Kansas

Here is what I have accomplished by doing it this way. When we create a new app I have a stored procedure which creates the LOGIN, DatabaseUser for all needs database and in each of the databases it creates the ROLE, SCHEMA and sets up the permissions for the securables within the schema.
Then in the compare for the Role I only have a single DatabaseUser.
When we deploy I have a script that updates the DatabaseUser with the correct enviornments login.

-- DEV Instance  -- CREATE SCRIPT

IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'MyDomain\svc_devTest')
CREATE LOGIN [MyDomain\svc_devTest] FROM WINDOWS
GO
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = N'Svc_Test')
CREATE USER [Svc_Test] FOR LOGIN [MyDomain\svc_devTest]
GO

-- QA Instance -- CREATE SCRIPT

IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'MyDomain\svc_qaTest')
CREATE LOGIN [MyDomain\svc_qaTest] FROM WINDOWS
GO
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = N'Svc_Test')
CREATE USER [Svc_Test] FOR LOGIN [MyDomain\svc_qaTest]
GO