Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Azure SQL: Create login user only if not exists

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

like image 626
Sourcerer Avatar asked Aug 24 '18 10:08

Sourcerer


People also ask

How do I create an SQL authentication user in Azure SQL database?

Connect to your Azure SQL Database server with SSMS as an admin and choose the database you want to add the user(s) to in the dropdown. Create a SQL authentication contained user called 'test' with a password of 'SuperSecret! ' then adding it to the db_datareader and db_datawriter roles.

How do I give a read only access to Azure SQL database?

To make this user read-only, we need to assign a role. This is done by granting this user the “db_datareader” role under the selected database context. Now that we have understanding on what the script would do for us, we can execute the script and create the read-only user account.

Can we create user without login in SQL Server?

The WITHOUT LOGIN clause creates a user that isn't mapped to a SQL Server login. It can connect to other databases as guest. Permissions can be assigned to this user without login and when the security context is changed to a user without login, the original users receives the permissions of the user without login.


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;
like image 68
Dan Guzman Avatar answered Oct 12 '22 14:10

Dan Guzman