Re: Execute AS DBO
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Fri, 31 Jul 2009 09:41:14 +0000 (UTC)
pardhiveswar via SQLMonster.com (u8956@uwe) writes:
CREATE procedure [dbo].[usp_SelectTable]
with execute as 'dbo'
as
select top 1 *
from LS_FAIL.master.dbo.syslogins
ls_fail is the liked server name and i am using 3rd option under linked
server security
HI i am not able to query the above stored procedure using EXECUTE AS DBO
i am getting error like Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
When you impersonate a user, which is what you do here, you are
sandboxed into the current database, and cannot perform actions outside.
To change this, you can make the database trustworthy:
ALTER DATABASE db SET TRUSTWORTHY ON
Making a database trustworthy is non-controversial, if you are the DBA and
and the database owner. But if the database owner is not sysadmin,
there is all reason to be careful with making the database trustworhy,
since it opens a couple of doors for the database owner.
Rather than using EXECUTE AS, it may be better to use certificate signing
to achieve what you want to do. I have an article on my web site that
discusses this in detail: http://www.sommarskog.se/grantperm.html.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Execute AS DBO
- From: pardhiveswar via SQLMonster.com
- Execute AS DBO
- Prev by Date: Re: Linked Server Security
- Previous by thread: Execute AS DBO
- Index(es):
Relevant Pages
|
