CODEWITHSUNDEEP
databaseazureauthenticationazure-sql-database
Lutze
Lutze

Reputation: 76

Create User failed by master db on Azure

I create a db on azure portal. After this I connect to the azure db with my own application which i programming in dotnet. What i want is to create a new login which have the permission to also create new logins. So i connect to master db, then i create a new SQL login, then i create a new user from the login and add them to the loginmanager role. So know i can create new logins with the user but when i want create also user from login then i get an error that i have no permission to alter the the login. So what can i do?

Thanks for helping Daniel

Upvotes: 1

Views: 1653

Answers (1)

Nick Trogh
Nick Trogh

Reputation: 116

The problem is that the loginmanager role doesn't have the necessary permissions to create or alter users. The 'CREATE USER' statement requires the 'ALTER ANY USER' permission (details here).

So, in the first step you create a login and user in the master database that has the 'loginmanager' role.

-- connect to the master database with your 'sa' account
CREATE LOGIN login1 WITH PASSWORD='<your password>';
CREATE USER login1user FROM LOGIN login1;
EXEC sp_addrolemember 'loginmanager', 'login1user';

In the second step you need to grant this user the 'ALTER ANY USER' permission. Note that this needs to be done in the application database in which you want to have the user accounts.

-- connect to the application database with your 'sa' account
CREATE USER login1user FROM LOGIN login1;
GRANT ALTER ANY USER TO login1user;

You should now be able to create new logins and the associated users. Note that you create the logins in the master database, and you create the user in the application database.

Upvotes: 1

Related Questions