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
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.
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.
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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With