-1

Is it possible to create a Server Login with a Master database user that is able to create a Server Login with a Master database user ?

I have not been able to do this, I always need to use the Admin user that was initially created when the server and first database was created.

Here is an example:

With Master DB Connection and logged in with Admin user

CREATE LOGIN Admin_Test WITH PASSWORD = ''
CREATE USER Admin_Test FROM LOGIN Admin_Test
EXEC sp_addrolemember 'loginmanager', 'Admin_Test';

With Master DB Connection and logged in with Admin_test user

CREATE LOGIN User1 WITH PASSWORD = ''
CREATE USER User1 FROM LOGIN User1

This fails with: User does not have permission to perform this action.

It seems that the only user allowed to create Users in Master Database is using the Admin user created when the Server and first Database was created ?

astaykov
  • 206
  • 1
  • 8
Cebben
  • 1
  • 1
  • 2

1 Answers1

1

The 'loginmanger' role doesn't grant the ability to create/alter users. You need to grant 'Admin_Test' the ability to 'Alter Any User'

GRANT ALTER ANY USER TO Admin_Test

You should examine whether or not you need a user in your logical master creating new users, though. While you may have a model which works for this, you're exposing additional surface area for escalations, so make your choice an educated one.

See these two links for additional info:

Managing Databases and Logins in Azure SQL DB

GRANT Database Permissions (Transact-SQL)

Chris Anderson
  • 111
  • 2