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?

Supramolecular

Answer 1

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

Orthodox

Answer 2

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

Synonymous

Answer 3

Problems like this that arise after moving a database from one machine to another usually involve mismatched SID's, although I'm not sure if or how it applies to your case. Try dropping and re-creating the database user, making sure to reinstate its permissions to those tables.

Logogram

That's just the point - this user has never had a login. What SIDs? And, as I said in the question above, I'd rather not recreate the user, because then I have to recreate a whole bunch of triggers as well. – Supramolecular

This worked for me. We had to migrate the database from another server and ran into this issue. – Denitadenitrate

Answer 4

It is an "orphaned user". It wont work. Documentation states this clear. :-( Fix "orphaned user" state and it will work again

Craigie

Which documentation - could you provide a link? Thanks – Brokerage