Grant permissions to a user to manage permissions of another user

SQL Server Administration
1

Hello,

I need to give alter login permissions to user1 (who is not a super user) for user2 so user1 can enable or disable user2 login.

I granted user1 permissions -
USE master;
GRANT CONTROL ON LOGIN::User2 to User1 WITH GRANT OPTION;
GO

Then User1 logged in and executed this command, however it didn't work.

use Master
go
REVOKE Connect SQL From User2
go

Error: Grantor does not have GRANT permission.

Can someone please help?

Thanks
-Amy

2

Have you tried Control Server option

USE master;
GRANT CONTROL SERVER TO user1
GO
3

Control server will give control to the entire server not just to manage login of this user. I just need one user managing login of another user primarily enable or disable operations.

4

You are correct it will give user the same as sysadmin temporarily, can user1 run this command from your original GRANT.

ALTER LOGIN user2 DISABLE
5

I tried but here is the error:

Cannot alter the login '', because it does not exist or you do not have permission.

6

Does anyone have a way to accomplish this task without giving sysadmin access to the user? Please let me know.

7

According to this MS documentation you need to grant ALTER ANY LOGIN
So for example:

GRANT ALTER ANY LOGIN TO Login1 WITH GRANT OPTION

8

This is again giving too much as the user may alter service account or sa login :frowning:

9

I think you are stuck with granting the ALTER ANY LOGIN permission with all the baggage/extra permissions that come with it.

An alternative might be to create a stored procedure with the code to enable/disable Login2 with the "EXECUTE AS" option using a login that has the elevated permissions. Then grant execute permission on that stored procedure to the low-level permissioned Login1.

Theoretically it should work, but I haven't tried it.

10

Thanks JamesK for he suggestion

However it's not going to work in my case as the user will have execute SP permissions as well as DML permissions for SQL code deployment. So he can modify the SP and may be cause threat or audit concern.

11

You can DENY specific permissions on specific objects. So perhaps you can deny ALTER permissions on this stored procedure.

12

I am not able to put my finger on it, but the requirement that a login has to have a wide range of permissions, and ability to alter just one other login seems like a it is less than robust design/security setup/something else. Perhaps if you took a step back and looked at your goals, there might be some way to achieve the end result that you are looking for in a simpler manner? I don't know, I am just asking/suggesting.

13

User1 is not a DBA however will be performing SQL deployments which should allow him to alter objects (no SQL jobs, logins, configuration etc) and data.

Before the deployment begins he has to disable application login (SQL login User2) to stop user's using application. Hope this is clear now.

I will perhaps try deny alter on specific sproc to User1 and this sproc will alter the login for User2,

14

Yep you would ned to look at hard coding something, Id create a role for this and assign permissions to objects that way if thats case.