Re: Select Permission Denied On Object

What is the right way to set up permissions so that a user can
execute a stored procedure in one database that does a select on a
table in another database?

Permissions on indirectly referenced objects are not needed as long as the ownership chain is broken. For dbo-owned objects in different databases, this requires that both databases have the same owner so that the 'dbo' user maps to the same login. Also, the 'db chaining' database option (introduced in SP3) need to be turned on so that SQL Server will honor the cross-database ownership chain. For example (assuming your objects are owned by 'dbo'):

1) enable the 'db chaining' database option in both databases:

EXEC DB1.sp_dboption 'db chaining', true
EXEC DB2.sp_dboption 'db chaining', true

2) Make sure both databases have the same owner:

EXEC DB1..so_changedbowner 'SomeLogin'
EXEC DB2..so_changedbowner 'SomeLogin'

Be aware of the security implications before your enable cross-database chaining. Users with object create permissions can elevate their level of access to data in other databases. Enable 'db chaining' in an sa-owned database when only sysadmin role members can create dbo-owned objects. See the Books Online for more information.

--
Hope this helps.

Dan Guzman
SQL Server MVP
"CSAWannabe" <wisertime2006@xxxxxxxxx> wrote in message news:1160604800.709446.16890@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Guys,

I have a stored procedure in one database, that is doing a select
statement on a table in a different database on the same server.

I gave execute permission on the stored procedure to a particular
user. When that user executes the stored procedure they get the
following error:

'Select Permisssion Denied On Object: TableName' The TableName
is a table in the different database. If I grant 'select' permission
on the table in the different database to the user, then everything
works.. But I would rather not grant any permissions to the user on the
table, and limit the permissions to simply execute on a stored
procedure.

What is the right way to set up permissions so that a user can
execute a stored procedure in one database that does a select on a
table in another database?

I'm running MS SQL Server 2000 on Windows 2000.


.

Relevant Pages

  • Re: Execute Persmission denied on object sp_OACreate
    ... SQL Server doesn't check permissions on indirectly referenced objects as ... You can prevent ad-hoc execution of powerful master database procs while ... >I have a user who has execute permissions on a store procedure in a>database> which in turns executes 4 stored procedures in the master database. ...
    (microsoft.public.sqlserver.security)
  • Re: public role question
    ... Who is the owner of the database? ... "Dan Guzman" wrote: ... Windows group and thereby get sysadmin permissions. ... Then I am able to create a stored procedure. ...
    (microsoft.public.sqlserver.security)
  • Re: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)
  • SQL 2000 Windows Authentication - Same User Multiple Groups
    ... view-level permissions such that we can permit/deny a database action ... Execute permission on UpdateResearch to only IT (and explicitly Denied ... Windows group and we have assigned the appropriate group permissions on ...
    (microsoft.public.sqlserver.security)
  • Re: public role question
    ... I logged on to the database through QA as dantest. ... "Dan Guzman" wrote: ... Windows group and thereby get sysadmin permissions. ... Then I am able to create a stored procedure. ...
    (microsoft.public.sqlserver.security)