Creating a self-signed certificate user with permissions to create logins, and also specific permissions in database

Using SQL Server Dev 2019 on my computer, SQL Server Standard 2019 on the client's computer.

My ultimate goal is to have a stored procedure that can be used to create logins, manage database role membership, and do a bunch of other stuff as well (but those are the main things). This procedure is already written and works fine... when I run it as a sysadmin. But I want it to work when anybody who's a member of the UserAdmin role (a custom database role that has execute permissions on the procedure) runs it.

So that's the goal. I'm in the process of making the procedure into a certificate-signed stored procedure. Here's what I have so far:

USE master; CREATE CERTIFICATE UserAdminCer ENCRYPTION BY PASSWORD = 'MyPasswordGoesHere1234' WITH SUBJECT = 'User Admin access', EXPIRY_DATE = '12/31/2922'; -- Error 3701 will occur if this date is not in the future GO CREATE LOGIN UserAdminAccount FROM CERTIFICATE UserAdminCer; GO CREATE USER UserAdminAccount FROM CERTIFICATE UserAdminCer; GO GRANT CONTROL SERVER TO UserAdminAccount; GO 

So my question is: what now? The stored procedure I need to grant access to is not in master; it's in another database. Do certificates in master carry over into other databases on the server? And if so, do I need to also do:

USE MyDb; -- do I need to do this part? CREATE USER UserAdminAccount FROM CERTIFICATE UserAdminCer; GO -- I assume I do need to do this... GRANT EXECUTE ON usradm.udp_ManageLogin TO UserAdminAccount; GO ADD SIGNATURE TO usradm.udp_ManageLogin BY CERTIFICATE UserAdminCer WITH PASSWORD = 'MyPasswordGoesHere1234'; GO 

So, my question is: what, if anything, should I do to make sure that UserAdminAccount has permissions to do all of the following tasks that are performed within MyDb.usradm.udp_ManageLogin:

1. create logins
2. create users
3. select from master.sys.server_principals and MyDb.sys.database_principals
4. add and remove those users from database roles in MyDb
5. select, insert, and update a few tables in MyDb.

...and is there anything else I should know to do? Thanks. :)

1 Answer

Here's how I'd do it:

• In the user database

  • create certificate
  • create user from certificate
  • add cert-based user to db_owner role†
  • add signature
  • grant execute to whomever you want to be able to run the proc

• In master

  • export the public key from the cert in the user db and import into master††
  • create cert-based login
  • add cert-based login to securityadmin server role

I'm 97% confident that's all you need (I'll test later to bring that to 100%). When I've done this sort of thing in the past, I use one cert for all of the user databases; being in the certificate management business wasn't important to me.

† I'm lazy, but this isn't so bad since the only way that this user can do anything is either through impersonation or through stored procs that are signed with the relevant cert. If you want you can find the minimal set of permissions to administer users in the user database and apply that instead of db_owner.

†† You shouldn't need the cert's private key in master because you're not using it to sign anything. But it doesn't hurt anything to have it there.