Error with SQL Server "EXECUTE AS"

Question

I've got the following setup:

There is a SQL Server DB with several tables that have triggers set on them (that collect history data). These triggers are CLR stored procedures with EXECUTE AS 'HistoryUser'. The HistoryUser user is a simple user in the database without a login. It has enough permissions to read from all tables and write to the history table.

When I backup the DB and then restore it to another machine (Virtual Machine in this case, but it does not matter), the triggers don't work anymore. In fact, no impersonation for the user works anymore. Even a simple statement such as this

exec ('select 3') as user='HistoryUser'

produces an error:

Cannot execute as the database principal because the principal "HistoryUser" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I read in MSDN that this can occur if the DB owner is a domain user, but it isn't. And even if I change it to anything else (their recommended solution) this problem remains.

If I create another user without login, I can use it for impersonation just fine. That is, this works just fine:

create user TestUser without login
go
exec ('select 3') as user='TestUser'

I do not want to recreate all those triggers, so is there any way how I can make the existing HistoryUser work?

Answer 1

What user account does the trigger execute as.

You'll need to grant that user IMPERSONATE priviledges for the User Account HistoryUser.

 GRANT IMPERSONATE ON USER:: YourUser TO HistoryUser

More details here

http://msdn.microsoft.com/en-us/library/ms181362.aspx

Answer 2

Detect Orphaned Users, then resolve by linking to a login.

DETECT:

USE <database_name>;
GO;
sp_change_users_login @Action='Report';
GO;

RESOLVE:
The following command relinks the server login account specified by <login_name> with the database user specified by <database_user>:

USE <database_name>;
GO
sp_change_users_login @Action='update_one',
@UserNamePattern='<database_user>',
@LoginName='<login_name>';
GO

https://msdn.microsoft.com/en-us/library/ms175475.aspx