Create a login which can create new logis & roles and can grant access to user on any database on that instance

Question

Hi All,

I want to create a login which can create new logins & roles and can grant/revoke access to user on any database on that instance without providing "sysadmin" access.

I have tried to provide user with "securityadmin" as server level role to the user and also provided the database level roles - "db_securityadmin" & "db_accessadmin", but is not helping.
Can anyone please suggest where i am missing, or what can be done.

Thanks,
Devendra Yadav

Answer 1

Going back to the original question, membership securityadmin on server level and membership in db_securityadmin and db_accessadmin should be sufficient with one reservation: the "in all databases" part. You need to add the login in all databases and add them to these two roles in all databases. The script below demonstrates.

As for securityadmin being able to elevate to sysadmin, I was not able to do this in a quick test as testified by the script below, but there maybe some more roundabout way I have not thought of.

-- Setup part
USE master
CREATE DATABASE testing_db
go
CREATE LOGIN Igor WITH PASSWORD = '"Köjkö][]++'''
ALTER SERVER ROLE securityadmin ADD MEMBER Igor
go
USE testing_db
go
CREATE USER Igor
ALTER ROLE db_securityadmin ADD MEMBER Igor
ALTER ROLE db_accessadmin ADD MEMBER Igor
go
CREATE TABLE ATable(a int NOT NULL)
go
USE master
go

-- Testing part. We impersonate Igor and try create login and a database user.
EXECUTE AS LOGIN = 'Igor'
go
CREATE LOGIN IgorTesting WITH PASSWORD = ')JBKÖLdfg"'
go
USE testing_db
go
CREATE USER IgorTesting
GRANT SELECT on ATable TO IgorTesting
go

-- No error messages so far. Let's go back to be master and try to be evil.
USE master
go
CREATE LOGIN IgorsEvilTwin WITH PASSWORD = 'U/&&??'
-- This fails, Igor cannot maipulate sysadmin.
ALTER SERVER ROLE sysadmin ADD MEMBER IgorsEvilTwin
go
-- Nor in this way.
EXEC sp_addsrvrolemember 'Igor', sysadmin
go
REVERT
go
-- Cleanup
DROP DATABASE testing_db
DROP LOGIN Igor
DROP LOGIN IgorTesting
DROP LOGIN IgorsEvilTwin

Answer 2

"not helping" means what in detail?
Membership of the server "securityadmin" is quite enough to manage logins, see Server-Level Roles

Answer 3

Note that if you are a securityadmin, you can make yourself a sysadmin! Be careful with securityadmins.

Answer 4

Rather than adding the person to a role or grant the permissions which may be too far-reaching, an alternative is to put the precise actions you want the user to perform in a stored procedure, which you sign with a certificate create a login from that certificate and grant that login the permissions needed. That certificate login is not a real login that can connect to the server, it only connects certificate and permission.

I describe this technique in detail in an article on my web site:
http://www.sommarskog.se/grantperm.html