Impersonation in SQL Server Views?

sql-server, sql-server-2008, sql-server-2008-r2

Solution

No, this is not possible. EXECUTE AS is mainly used with SP's, but you can use them a bit more widely. From TechNet:

In SQL Server you can define the execution context of the following user-defined modules: functions (except inline table-valued functions), procedures, queues, and triggers.

...

Functions (except inline table-valued functions), Stored Procedures, and DML Triggers { EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }

DDL Triggers with Database Scope { EXEC | EXECUTE } AS { CALLER | SELF | 'user_name' }

DDL Triggers with Server Scope and logon triggers { EXEC | EXECUTE } AS { CALLER | SELF | 'login_name' }

Queues { EXEC | EXECUTE } AS { SELF | OWNER | 'user_name' }

However, you have some options here:

- create GET-SP's that return your data and UPDATE-SP's that upate your data (I use XML input for this instead of table-variables)

- use views created by your 'impersonated' user, and play with permission inheritance breaking using DENY/GRANT, like GRANT VIEW DEFINITION

Problem

Is it possible to create views with impersonation, similar to `"execute as"` in stored procedures? I would like to create some views in a separate schema. Some users should get `SELECT` and `UPDATE` access to these views, so that they are able to change the underlying tables, but without having direct update access to the table. Is that possible with a view ?

Original source