CODEWITHSUNDEEP
sql-serverimpersonation
Vilx-
Vilx-

Reputation: 107072

Error with SQL Server "EXECUTE AS"

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?

Upvotes: 9

Views: 39770

Answers (4)

GretTNelson
GretTNelson

Reputation: 61

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

Upvotes: 6

Krzemo
Krzemo

Reputation: 27

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

Upvotes: 1

Jeff Mattfield
Jeff Mattfield

Reputation:

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.

Upvotes: 4

Eoin Campbell
Eoin Campbell

Reputation: 44306

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

Upvotes: 5

Related Questions