Using 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)

Using Execute As

Author

Topic

mfemenel
Professor Frink

1421 Posts

Posted - 2007-10-11 : 14:19:14

I'm not quite getting how to use execute as. here's my problem. I am calling a web service from microsoft crm 3.0. When I watch profiler, it comes in with a login of XYZ (let's pretend). Well, XYZ doesn't have permissions to the records in the view so it returns nothing. Now I copy the same query from profiler, put it into query analyzer and run it, it comes back with records because my login has permissions to the records in the view. Well, sounds like a great use of Execute As to me, but I've not used it before. So, somewhere in the stored procedure I need to tell it, don't run as qmdev, run as me instead (or another login with the right record permissiosn). When I try to execute my code from query analyzer, I get an error.

Execute as login='[QMDEV\CRMService]'
exec Dedupe_Lead_Wrapper @FirstName=N'J',@LastName=N'',@DOB=NULL,@Zip=NULL,@Phone=NULL

Error: Msg 15404, Level 16, State 11, Line 2
Could not obtain information about Windows NT group/user '[QMDEV\CRMService]', error code 0x534.

When I'm running this I am remote desktopped(is that a word?) into the database server on the QMDEV domain, CRMService is absolutely a login in the database and is a DBO with permissions to the database I'm trying to access within the SP. Incidentally when I tried to put the execute as statement as part of the stored procedure create statement, I got the same issue.

So I'm fairly stuck as to what to try next. Any ideas?

Mike
"oh, that monkey is going to pay"

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-11 : 22:55:17

Is QMDEV\CRMService a valid windows account in QMDEV domain? Tried with other account in that domain? If not, seems that sql server has problem to talk to domain controller.