How to use 'execute as ' - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to use 'execute as '

Author

Topic

chih
Posting Yak Master

154 Posts

Posted - 2008-02-27 : 22:53:14

Hi Everyone,

I have problem in applying 'execute as'.

Here is an exsample SP
Create procedure test
with execute as 'user1'
as
select * from people.dbo.profile

I have 2 accounts: user1 and user2.
user1 has permission to select data from people.dbo.profile but user2 doesn't. due to some reasons I don't want to grant select permission to user2.

I use user2 to execute this SP and get error message

The server principal "user1" is not able to access the database "people" under the current security context.

How can I solve it? I have been looking for solution for the whole morning.

Thank you in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 01:22:10

quote:

Copied from Books Online

CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS 'CompanyDomain\SqlUser1'
AS
SELECT user_name();
GO

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources