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.