CREATE ANY LOGIN permission?

Question

CREATE ANY LOGIN permission?

Ken McKelvey

SSCoach

Points: 18842
More actions
April 5, 2019 at 11:20 am

#2857095

I am trying to allow a login to create other logins but not be able to alter or drop logins which already exist.
The closest I seem to be able to get is the ALTER ANY LOGIN permission but this allows control of all logins.
As there is no CREATE ANY LOGIN permission does anyone know a way of getting this functionality? Maybe by assigning ALTER ANY LOGIN but somehow restricting permission to ALTER or DROP a login?
Thanks.
ps The login will also have the CREATE ANY DATABASE permission.
ie The use case is that the login will be able to create a database and then create logins, with minimal permissions, which it can then use to create users in the database.
April 9, 2019 at 4:56 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Ken McKelvey wrote:

I am trying to allow a login to create other logins but not be able to alter or drop logins which already exist.
The closest I seem to be able to get is the ALTER ANY LOGIN permission but this allows control of all logins.
As there is no CREATE ANY LOGIN permission does anyone know a way of getting this functionality? Maybe by assigning ALTER ANY LOGIN but somehow restricting permission to ALTER or DROP a login?
Thanks.
ps The login will also have the CREATE ANY DATABASE permission.
ie The use case is that the login will be able to create a database and then create logins, with minimal permissions, which it can then use to create users in the database.

---------------------------------------------

Another option that would work is to create a stored procedure that uses dynamic sql and whichever parameters you use to build the create login statement.

Then sign that stored procedure, give alter any login permissions to the login created from the certificate used in signing the stored procedure and then grant execute on that stored procedure to the login you want to be able to create logins. So that user doesn't have direct permissions to anything with the logins - just execute permissions on the signed procedure that creates logins.

The process isn't as confusing as it seems when you run through it a couple of times. There is a tutorial in the documentation for signing stored procedures:

https://docs.microsoft.com/en-us/sql/relational-databases/tutorial-signing-stored-procedures-with-a-certificate?view=sql-server-2017

Sue

Ken McKelvey SSCoach Points: 18842 More actions · Answer 2

Thanks Sue. This sound like a good idea; I will look into it.

Joe Torre SSChampion Points: 10248 More actions · Answer 3

A database trigger on master rolling back drop login might work as well based on role membership.