Post reply

EXECUTE AS not working as expected

  • July 30, 2007 at 3:31 pm

    #179948

    I'm trying to create a stored procedure in SQL Server 2005 that will be used by account managers to rename logins.

    While we used SQL Server 2000, sysadmins had to do this. I created a nice procedure to do the renaming, but it's not working the way I'm expecting it to.

    I run a series of checks in the procedure to ensure that

    a) the old user exists in the database (sys.database_principals)

    b) the new login name doesn't exists (sys.sql_logins)

    c) the user isn't logged in (sys.sysprocesses) and some other things.

    At first I was doing "EXECUTE AS SELF" and since I was compiling the procedure as 'sa' this should have worked. The problem is that the sys metadata views like sys.sql_logins and sys.sysprocesses are only returning one record each; the record for the EXECUTE AS person. This is annoying. Obviously if I select * from sys.sysprocesses when logged in as 'sa' I get all of the records; same with sys.sql_logins. What's the deal with this? I've read through a lot of Books Online and can't find the answer. Typical Microsoft coding examples are overly simplistic and don't address any real world applications. They're like: EXECUTE AS someotheruser; select user_name(). @_@

    I've also tried EXECUTE AS OWNER, and EXECUTE AS 'loginmanager' which is a sysadmin account I created just as a test in case there were some special provisions regarding sa and dbo. Anyone know what's going on here? I figured this would be like using suid in Linux, but clearly it just can't be that easy and straightforward...

  • August 3, 2007 at 7:32 am

    #724500

    It is quite an interesting problem. It seems to do something with ownership chaining. The sys.sql_logins system view looks up items in the master database (master.sys.sysxlgns, you can see this via an admin connection only).

    When you have a proc like

    CREATE PROCEDURE proc1 
WITH EXECUTE AS self
AS
BEGIN
SELECT CURRENT_USER AS Me, name FROM sys.sql_logins
END

    it returns, as you state, only a single row when executed as dbo. Same happens when you specify a user explicitly in the execute as clause. However, if you set the execute as to caller, and call this stored procedure as a dbo, you do get the complete result set. Interestingly, if you create this stored procedure in the master database , it seems to return the full result once again, independently whether you are using "self", "'dbo'" or owner as part of the execute as clause. It feels like a bug in SQL Server.

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • August 3, 2007 at 1:12 pm

    #724624

    I found the answer, I forgot to post here. I hadn't set the database as TRUSTWORTHY. :/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply