Issue with CREATE LOGIN

Question

I have an issue with permission required for CREATE LOGIN .

I create a login, grant it securitylevel server role, db_securityadmin on master DB. Let's call it LoginA

LoginA is able to create new logins on the server ( ServerL)

But on other server ( ServerP), LoginA cannot create new logins. It can only create when I grant it db_onwer on master DB.

Both servers have same build. 15.0.4261.1

Only difference is ServerP & ServerL are in different domains. ServerP is in prod domain, ServerL is in lower domain.

What could be reason for this issue ?

Answer 1

First, db_securityadmin is a database role, and not a server role, so membership in that role cannot give you rights to create logins. And same goes for db_owner. And you cannot add a login to db_owner, for that you need a database user.

Here is an example of what you should do:

USE master
go
CREATE LOGIN lakritstomte WITH PASSWORD = 'sdgsdfhdfharfhn'
go
ALTER SERVER ROLE securityadmin ADD MEMBER lakritstomte
go
EXECUTE AS LOGIN = 'lakritstomte'
go
CREATE LOGIN extralogin WITH PASSWORD = 'sfdgsdhfearhaerh'
go
REVERT
go
IF suser_id('extralogin') IS NOT NULL
   DROP LOGIN extralogin
go
DROP LOGIN lakritstomte

Note that this uses the server role securityadmin.

On the other hand, adding a user to db_owner, is not sufficient to create logins:

USE master
go
CREATE LOGIN lakritstomte WITH PASSWORD = 'sdgsdfhdfharfhn'
go
CREATE USER lakritstomte
ALTER ROLE db_owner ADD MEMBER lakritstomte
go
EXECUTE AS LOGIN = 'lakritstomte'
go
CREATE LOGIN extralogin WITH PASSWORD = 'sfdgsdhfearhaerh'
go
REVERT
go
IF suser_id('extralogin') IS NOT NULL
   DROP LOGIN extralogin
go
DROP USER lakritstomte
DROP LOGIN lakritstomte