If you are a concerned DBA, you know that the rule of “least privilege” must be followed at all times. Even if something is not perfect right now, this is the direction to go and every step in that direction is worth it.
But suppose that you need to share some administrative tasks with more people, what do you do? Give them SA rights is out of the question, as only the members of the DBA team should have it. So you must try to give only the strict necessary rights to do exactly what they should do. And if this task is “manage SQL logins for a application” it would mean give SecurityAdmin server-wide role to someone. No sir, this role is too powerfull as it allows it’s members to change almost ALL logins on the instance, and not just the ones specifics to some application.
Therefore we must build some logic to enable this, and as this situation is not so common I thought of building one small tutorial to make it work. And it works: a simple non-privileged user is capable of managing specific server logins sucessfully, without risks of compromising other logins.*
For this recipe you will need:
- One non-privileged User
- One Login that will have the SecurityAdmin server role
- Some procedures
- A few and rare “grant” type
- Any database to hold the procedures. If it is exclusive to administrative things, the better.
Start by creating the Login that will have the SecurityAdmin right. Not even you needs to know it’s password, because it will only be impersonated and never used by anyone. Then create the non-privileged-User, map it to the database where your procedures will be, and issue this command:
USE MASTER
go
grant impersonate on login::SecurityLogin to SimpleUser
Then create the required procedures on the chosen database. Put whatever logic you need to prevent that the User has any possibility of messing with logins that you do not want him to touch. Grant only execute on these procedures to SimpleUser. Remember to include “EXECUTE AS LOGIN = ” to change the execution context before accessing administrative tables or functions (the more usual “execute as user =” wont work, this is the trick). Sample code is below, tailor it to your needs - but if you want to share it please include it’s source 
*Of course the User could impersonate the SecurityAdmin login, but only if he manages to see the sourcecode of the procedure or list the login in the first place. Two things that you want to make sure he can NOT do. And with this approach, he will not be able to see anything on SSMS, he will only ever see what you show him though the procedures (Oh, and please include SQL-injection protection too, I’m in a hurry over here).
create procedure [dbo].[DBA_ListLogins]
as
EXECUTE AS LOGIN = 'SecurityAdmin_Login'
select distinct(spri.name), is_disabled
from master.sys.server_principals spri
where spri.sid > 100
and spri.name not like '##%'
and spri.name not like 'Security%'
and spri.name not like 'DBA%'
order by name
REVERT
go
create procedure [dbo].[DBA_DisableLogin] (@Login varchar(50))
as
begin
if (@Login = 'DBA') or (@Login = 'SA')
or (@Login = 'SecurityAdmin_Login')
raiserror ('ERROR - Unauthorized Operation.',10,1)
else
EXECUTE AS login = 'SecurityAdmin_Login'
declare @sql nvarchar(100)
set @sql = N'ALTER LOGIN [' + @Login + N'] DISABLE'
exec sp_executesql @sql
REVERT
end
go
and now the error message that you may find if you forget to grant that “impersonate”:
Cannot execute as the server principal because the principal
does not exist, this type of principal cannot be impersonated,
or you do not have permission.
Update: included the brackets to avoid SQL Injection.
