Azure SQL: Create login user only if not exists

Question

I'm trying to create an automated script for creating Azure SQL database over an already existing instance.

I know I can use the automation based in CLI, but I need a little more control about what is created and when.

Now I'm stopped in the login creation. I want to create the login user only if not exists (and alter the password if exists).

This is my code:

IF NOT EXISTS (SELECT name FROM sys.sysusers WHERE name='$(dbUserName)')
    BEGIN
        CREATE LOGIN $(dbUserName) WITH PASSWORD='$(dbUserPassword)';
    END
ELSE
    BEGIN
        ALTER LOGIN $(dbUserName) WITH PASSWORD='$(dbUserPassword)';
    END

My problem comes that sys.sysusers views check database users, not login user. I'm trying to find which system catalog view contain login users, but I'm not able to find (I have also tried database_principals)

NOTE: I have found several alternative for SQL Server on-premise, but they don't work in Azure SQL

Dan Guzman · Accepted Answer · 2018-08-24 11:33:13Z

8

Logins must be created/altered in the context Azure SQL Database logical server master database. Use the sys.sql_logins catalog view for this task. The sys.sysusers view is provided only for backwards compatibility and should not be used going forward in either Azure or on-prem SQL Server.

IF NOT EXISTS (SELECT name FROM sys.sql_logins WHERE name='$(dbUserName)')
    BEGIN
        CREATE LOGIN $(dbUserName) WITH PASSWORD='$(dbUserPassword)';
    END
ELSE
    BEGIN
        ALTER LOGIN $(dbUserName) WITH PASSWORD='$(dbUserPassword)';
    END;

answered Aug 24, 2018 at 11:33

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sourcerer Over a year ago

I also tried this, but my master database has no sys.sql_logins object. Is it possible something special with the instance? (I didn't not created the instance, it was given to me by subscription admin)

Dan Guzman Over a year ago

@Sourcerer, I tested this and it works for me. @@VERSION returns "Microsoft SQL Azure (RTM) - 12.0.2000.8 Jul 24 2018 14:38:12 Copyright (C) 2018 Microsoft Corporation". Are you sure you are connected to the master database?

Sourcerer Over a year ago

My fault!!! :-/ I retested and worked like a charm (I don't know why failed first time)

Collectives™ on Stack Overflow

Azure SQL: Create login user only if not exists

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related