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.