Problem creating a stored procedure with the Execute as clause.

I have a server with a login named NewCreator. This login is assigned only to the dbcreator server role.

I want this login to be able to execute the stored procedure, sp_helplogins.

The sp_helplogins documentation says the caller of this proecedure needs securityadmin permissions.

I know that I should be able to create a login, say 'SecAdmin' and assign that login to the security admin server role, and then grant NewCreator Impersonate permission on SecAdmin.

What I don't like about this technique is that NewCreator can get on the server and gain complete authority over security when I really only want him to be able to execute sp_helplogins.

Therefore, what I am attempting is the following:

I create the login, SecAdmin, and assign it to the securityadmin server role.

Then I right click sp_helplogins and select 'Modify'.

I change the Alter to Create, change the name of the procedure to sp_myspecialone, and then add the sql statement "With Execute as 'SecAdmin'.

I execute the above and the new stored procedure is created. I then grant Execute on sp_myspecialone to NewCreator.

Everything works fine so far. But when I login as NewCreator and execute sp_myspecialone, I get an error in line 73 - no permission.

Line 73 is blank, but the next few lines read:

if (not (is_srvrolemember('securityadmin') = 1))

begin

raiserror(15247,-1,-1)

select @.RetCode = 1

goto label_86return

end

What am I doing wrong? Is their a better alternative to accomplish what I want to do?

Your assistance would be greatly appreciated.

It looks like when executing "AS SecAdmin" you are only executing the proc in the context of the user not the login. Therefore, it won't resolve the server permissions of the login SecAdmin but just the database permissions of the user SecAdmin.

In your scenario check:

execute as login = 'SecAdmin'

SELECT is_srvrolemember('securityadmin')

execute as user = 'SecAdmin'

SELECT is_srvrolemember('securityadmin')

I'm afraid i don't have any alternatives at this time but i'll have a think.

Hope this helps for now.

|||

Actually, is it possible to change the proc to be something along the lines of:

CREATE PROC blah
AS

EXECUTE AS LOGIN = 'SecAdmin'

EXEC sp_helplogins