How I can impersonate a login with securityadmin server role

Question

On MSSQL 2008R2 I have two instances on different servers, and I need sync up the password changes for all logins. I thing that i could create a store procedure where a user (impersonate) with securityadmin server role change the password.

Obs: CHANGE_PASSWORD is a user with securityadmin server role.

ALTER PROCEDURE SPU_CAMBIO_PASSWORD
(
    @USER VARCHAR(50),
    @PASSWORD VARCHAR(100)
)
WITH EXECUTE AS 'CHANGE_PASSWORD'
AS
    DECLARE @STATEMENT VARCHAR(200)
    SET @STATEMENT= 'ALTER LOGIN '+ @USER+ ' WITH PASSWORD = '''+@PASSWORD+''', CHECK_POLICY =OFF'
    EXEC(@STATEMENT)
GO

Error: Cannot execute as the server principal because the principal "CAMBIO_PASSWORD" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Answer 1

The bad news is that the EXECUTE AS clause (which you are using), as opposed to the statement, only works for Users (Database-level), and not Logins (Server-level).

BUT, the good news is that you don't need to use impersonation to solve this issue. Instead you need to use module signing, and you do that via the following steps (all done in [master]):

1. Create a Certificate (be sure to specify ENCRYPTION BY PASSWORD = 'password')
2. Create a Login from that Certificate
3. Grant that Login whatever permission you want, or add it to whatever Role is appropriate
4. Sign that Stored Procedure (i.e. SPU_CAMBIO_PASSWORD), after removing the EXECUTE AS clause
5. Backup that Certificate to files (a .cer file for the Certificate and a .pvk file for the Private Key). This is done so that it can be re-created later.
6. Remove the Private Key from that Certificate. This prevents anything else from being signed with it (so you better do that backup first!).

If that Stored Procedure is ever changed after signing it, the signature will be dropped automatically, thereby losing the association with those special permissions set up for that Certificate-based Login. This is a good thing as it allows you to do a code review (assuming you didn't make the change) before re-enabling the permissions. In this case, you would:

1. Restore the Private Key (using the same ALTER CERTIFICATE command that was used to remove the Private Key).
2. Sign the Stored Procedure
3. Remove the Private Key again