Create User failed by master db on Azure

Question

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

Nick Trogh · Accepted Answer · 2015-07-17 08:09:20Z

1

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.

answered Jul 17, 2015 at 8:09

Nick Trogh

1165 bronze badges

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

4 Comments

Lutze Over a year ago

Thank you Nick for answering. It clearify much things but I have one question. When I add login1user to loginmanager then the login1user have not the permission to add other users to the same role. The scenario is that the sa can delegate the login managment to an other user and this user can also delegate to an new user. The problem is that the SA cannot grant the ALTER ANY ROLE right to login1user. So the question is how it works? In this case is there any possibility to delegate the same rights of SA to an other user? after this we have one SA and another user with same priveleges.

Nick Trogh Over a year ago

As far as I know, you cannot add another server admin ('sa') user or emulate one by doing an ALTER ANY ROLE. You can only assign the loginmanager or dbmanager (creating DBs) at the server level. Inside of a database you can manage everything - not sure if you could get around using this?

Lutze Over a year ago

But in this case it seems to be a fail because when the SA add someone to the loginmanager role, then can the user add new logins but it has no rights to add someone also to the loginmanager role because there is no way to get this user the right to Alter Any User which is needed to add someone to role.

tmullaney Over a year ago

SA can grant alter on loginmanager to login1user while connected to the master database like this: GRANT ALTER ON ROLE::loginmanager TO login1user WITH GRANT OPTION; and then while connected to the application database, change the last line to: GRANT ALTER ANY USER TO login1user WITH GRANT OPTION;

Collectives™ on Stack Overflow

Create User failed by master db on Azure

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related