Cross Database Permission

I have a stored procedure that I created on one database with permission
granted to a select group of users. The proc grabs information from another
database on the same server. When users with permissions to the proc try to
run it, they get permission errors saying that they do not have permissions
to access the other database that is referenced in the proc. It was my
understanding that when you run a proc, it runs under the context of the proc
creator (in this case dbo), otherwise you have to grant select, insert,
update, etc. on all tables that your proc references. Does this not work
cross database?Clark,
You do not mention which version of SQL Server you are using, but both 2000
and 2005 have in the Books Online index an entry for "cross-database
permissions" which you should read.
Basically, the ownership-chain is broken at a database boundary as I quote:
"Cross-database permissions are not allowed; permissions can be granted only
to users in the current database for objects and statements in the current
database. If a user needs permissions to objects in another database, create
the user account in the other database, or grant the user account access to
the other database, as well as the current database."
There is a setting to turn on "cross database ownership chains", but that is
not recommended due to the (potentially serious) security side-effects.
RLF
"Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
news:92FF4E5C-9690-41AE-A4EA-362018397F79@.microsoft.com...
>I have a stored procedure that I created on one database with permission
> granted to a select group of users. The proc grabs information from
> another
> database on the same server. When users with permissions to the proc try
> to
> run it, they get permission errors saying that they do not have
> permissions
> to access the other database that is referenced in the proc. It was my
> understanding that when you run a proc, it runs under the context of the
> proc
> creator (in this case dbo), otherwise you have to grant select, insert,
> update, etc. on all tables that your proc references. Does this not work
> cross database?|||It is running on SQL Server 2000 with the allow cross-database ownership
chaining disabled. So I take it dbo does not have cross database access,
which makes sense now that I think about. Thanks.
"Russell Fields" wrote:
> Clark,
> You do not mention which version of SQL Server you are using, but both 2000
> and 2005 have in the Books Online index an entry for "cross-database
> permissions" which you should read.
> Basically, the ownership-chain is broken at a database boundary as I quote:
> "Cross-database permissions are not allowed; permissions can be granted only
> to users in the current database for objects and statements in the current
> database. If a user needs permissions to objects in another database, create
> the user account in the other database, or grant the user account access to
> the other database, as well as the current database."
> There is a setting to turn on "cross database ownership chains", but that is
> not recommended due to the (potentially serious) security side-effects.
> RLF
> "Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
> news:92FF4E5C-9690-41AE-A4EA-362018397F79@.microsoft.com...
> >I have a stored procedure that I created on one database with permission
> > granted to a select group of users. The proc grabs information from
> > another
> > database on the same server. When users with permissions to the proc try
> > to
> > run it, they get permission errors saying that they do not have
> > permissions
> > to access the other database that is referenced in the proc. It was my
> > understanding that when you run a proc, it runs under the context of the
> > proc
> > creator (in this case dbo), otherwise you have to grant select, insert,
> > update, etc. on all tables that your proc references. Does this not work
> > cross database?
>
>|||Clark Kent,
1 - Cross-Database Ownership Chaining should be enabled
2 - If the owner of the sp is the owner of the objects being referenced from
the other db, then there should be no problem, but if the owner of the sp
just has rights to "select", then SS will check if the one executing the sp
also has "select" right on those objects.
Ownership Chains
http://msdn2.microsoft.com/en-us/library/ms188676.aspx
SQL Server 2005 Security Overview for Database Administrators
download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/SQLSecurityOverviewforAdmins.doc
Giving Permissions through Stored Procedures
http://www.sommarskog.se/grantperm.html
AMB
"Clark Kent" wrote:
> I have a stored procedure that I created on one database with permission
> granted to a select group of users. The proc grabs information from another
> database on the same server. When users with permissions to the proc try to
> run it, they get permission errors saying that they do not have permissions
> to access the other database that is referenced in the proc. It was my
> understanding that when you run a proc, it runs under the context of the proc
> creator (in this case dbo), otherwise you have to grant select, insert,
> update, etc. on all tables that your proc references. Does this not work
> cross database?