Re: cross database query with ownership chaining

Mark (marksullivan@xxxxxxx) writes:
I want a SQL Account called SQLUser to call a stored
procedure in my non-CRM database to invoke a view that will perform the
union. The SQLUser has been granted Impersonate for
'''mydomain\WindowsUser'.

But exactly how did you do this? Did you say:

GRANT IMPERSONATE ON USER::[mydomain\WindowsUser] TO SQLUser

or did you:

GRANT IMPERSONATE ON LOGIN::[mydomain\WindowsUser] TO SQLUser

Since you do EXECUTE AS LOGIN when you impersonate, you must do the latter.

When I use the command, EXECUTE('EXECUTE AS LOGIN =
''mydomain\WindowsUser'';execute mydatabase..myprocedure) When not set to
sysadmin here is the error:

You could also do:

EXECUTE('EXECUTE mydatabase..myprocedure')
AS LOGIN = 'mydomain\WindowsUser'

to avoid a few extra single quotes.




--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: User Question
    ... and write capabilities. ... Should that be an SQL login or Windows login? ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)
  • Re: SQL 2005 express blank sa passwords
    ... that uses the Sa login, I need to set the password to blank. ... I suggest that you get a voodoo doll of that former DBA, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)
  • Re: Using sql to execute a batch file, xp_cmdshell
    ... In here I have entered the login and password for an accepted ... credential when I am running the bath file? ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: newbie Simple Question about Users in SQL 2000
    ... access with the roles public and db_owner for the database. ... I still get login failed for user d_xxxx. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: sql express 2005 security
    ... If they can login, they can view the data, right? ... And, yes, this means that the user will be able to connect to the database ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)