Execute As error

I have a stored procedure that needs to run with elevated privliges, so I tried setting it to run as one of the db owners. When a regular user tries to execute the SP, I get the error

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

What do I need to set up so a user can be impersonated?

Can you post a quick repro of the code you used to create the user, and to execute as? You might just try:

execute as user ='dbo'

Since that is the dbo. Or execute as SELF, if the object is owned by DBO.

|||

Here you go.

ALTER PROCEDURE [dbo].[ClearStaleLocks]

with execute as 'mpswaim'

AS

BEGIN

SET NOCOUNT ON;

create table #spids

(

spid int not null

)

insert into #spids select spid from master.dbo.sysprocesses

delete locks where spid not in (select spid from #spids)

drop table #spids

END

|||

Did you try as SELF? It should work like you want. mpswaim is a user, who is the db_owner group, right? You really shouldn't need impersonation for this procedure to work anyhow, since all users have rights to sysprocesses.

Here is the script I used to reproduce the situation. I created the users without login for ease of testing, but if they are attached to logins it should make any difference.

create table dbo.locks
(
spid int
)
go
create user mpswaim without login
go
exec sp_addrolemember 'db_owner','mpswaim'
go
create PROCEDURE [dbo].[ClearStaleLocks]
with execute as 'mpswaim'
AS
BEGIN
SET NOCOUNT ON;
create table #spids
(
spid int not null
)
insert into #spids select spid from master.dbo.sysprocesses
delete locks where spid not in (select spid from #spids)
drop table #spids

END
go
create user test without login
go
grant execute on [dbo].[ClearStaleLocks] to test
go
execute as user = 'test'
go
[dbo].[ClearStaleLocks]
go

|||

I need impersonation so the SP can see other users sessions. Let me try creating a user w/out a login and see what that does.

|||

I think you miss my point. This is what I would try next:

alter PROCEDURE [dbo].[ClearStaleLocks]
with execute as self
AS
BEGIN
SET NOCOUNT ON;
create table #spids
(
spid int not null
)
insert into #spids select spid from master.dbo.sysprocesses
delete locks where spid not in (select spid from #spids)
drop table #spids

END
go

Self should make it execute in the context of DBO and give you the rights you want. The loginless user was simply for me to simulate users in a database.

|||

If I try to impersonate dbo, I get the error,

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

|||Can you script out the users that you are using for this? Especially what roles they are in? I am thinking we are not quite connecting here. If you are building a procedure in the dbo schema, owned by dbo, and the user who is building the procedure is dbo, not to mention that you cannot drop the dbo user, then something is either wrong, or I am not hearing what you are saying (and that would not be the first time I have missed the point completely!)|||

Here's the user that I'm testing against.

CREATE USER [doolittle] FOR LOGIN [doolittle] WITH DEFAULT_SCHEMA=[dbo]

|||I've also tried granting impersonation privliges to doolitle. That didn't help either.|||

The reason why the impersonation doesn't work is due to the VIEW SERVER STATE permission. And this is a server level permission. This is required to view information for all SPIDS in sys.sysprocesses for example. And your impersonation is at the user level and hence it is restricted to the database resources only. There are ways to do this using a certificate login, grant server permissions to it, and signing your module with the certificate. Or set TRUSTWORTHY bit for the database ON depending on the impersonation model that you choose. BOL has the necessary examples to facilitate this scenario. Please see the following topics for more details and understanding how the impersonation context works.

http://msdn2.microsoft.com/en-us/library/ms188304.aspx

http://msdn2.microsoft.com/en-us/library/ms345102.aspx

The module signing topic (2nd link above) has the specific example that will help resolve your problem.