Azure SQL: Create login user only if not exists

user_7349864 0
Viewed 3605

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

azure-sql-database azure authentication sql-server
1 Answers

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;
user_37111620
Related