SETUSER Replacement for SQL 2008

• Greg

  Old Hand

  Points: 351

  More actions

  September 13, 2010 at 10:30 am

  #229225

  I am trying to replace the SETUSER with the EXECUTE AS as noted in the deprecated features list for 2008. However, I am running into some issues and questions.

  First, I have a stored proc (let's call it dbo.Proc1) under dbo that is called from AppUser1. When dbo.Proc1 is called it gets a list of all the users/owners and then calls each specific owners procedure (<user>.Proc2). Proc2 exists for each owner. So, in summary, dbo.Proc1 loops through and calls User2.Proc2, User3.Proc2, User4.Proc2 and so on.

  All of the users (User2, User3, User4) are in a role called DOMAIN. Also Proc2 calls several other procedures using the same owner (i.e. User2.someproc1, user2.someproc2).

  My question is how can I grant the role DOMAIN permission so that Proc1 can call Proc2 using the Proc2 owner's context? I need permisison granted at a ROLE level and not at a database user level otherwise I would use the GRANT IMPERSONATE sql. There is a possibility that users would be added to the DOMAIN role so I need it at a ROLE level.

  Thoughts or comments?

  Thanks,

  Greg

• Greg

  Old Hand

  Points: 351

  More actions

  September 13, 2010 at 1:46 pm

  #1220295

  Looks like I solved this issue by doing the following:

  Connected to the master as sa.

  Executed the command: GRANT IMPERSONATE ON LOGIN::mylogin to mycallerlogin;

  Then in my stored procedure did a EXECUTE AS LOGIN = 'mylogin'

  Made the execute statement.

  Then REVERT back to previous user before EXECUTE AS.

  Worked good!