question

MartinSeek avatar image
MartinSeek asked

Is it possible to GRANT non-SA user to create user for login in all databases?

Hi everyone,

I'm trying to grant a non-SA user the permission to execute this, without adding him to every single db in the instance and without granting him making changes to tables ecc:

USE [somedb]

CREATE USER [someuser] FOR LOGIN [someuser]

GO

Is it possible? Sure not a best practice, but still need it.

Thank you in advance,

sql-server
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

To do that without adding to each db in the instance would need a server-level role, possibly securityadmin, but as pointed out here that's pretty much the same as sysadmin: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver15 when it comes to user and server permissions.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

Server role - securityadmin

Database role - db_securityadmin

Or even better if you can create custom roles both server and database, just add them to a custom role which has only the permissions you want them to have.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.