Re: Select Permission Denied On Object

Would it be better from a security standpoint then to simply grant
select permission on the table in the other database???

It depends. If data are sensitive or you want to lock-down access to stored procedures and views, then it's probably be better to avoid direct select permissions. The main consideration with cross database chaining is that the security implications aren't obvious. That probably the main reason why it is turned off by default. However, in many envirironments, only sysadmins can create objects so the security issues are mitigated.

On the other hand, if data are not sensitive and direct selects are no big deal, go with select permissions. You can still encourage access only through procs, just not enforce it.

If I were to implement db chaining, what would be a good generic
'somelogin' value. i.e. 'sa'? I don't want to create another
account on the machine.

Yes, you can use 'sa' or any other existing login. Just keep in mind that 'sa' is the special case because 'sa' owns system database like master. As I mentioned earlier, specify 'sa' as then database owner only when dbo-object creation is restricted to only sysadmin users.


--
Hope this helps.

Dan Guzman
SQL Server MVP
"CSAWannabe" <wisertime2006@xxxxxxxxx> wrote in message news:1160654638.946371.82110@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Thanks for your reply.

Would it be better from a security standpoint then to simply grant
select permission on the table in the other database???

If I were to implement db chaining, what would be a good generic
'somelogin' value. i.e. 'sa'? I don't want to create another
account on the machine.





Dan Guzman wrote:
> 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: Permissions on sp_OASetProperty
    ... this be setup with the least amount of permissions being given. ... chaining to allow limited access to the sp_OA* procs and other potentially ... dbo-owned objects in that database. ... EXEC sp_dboption 'MyDatabase', 'db chaining', true ...
    (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: Security - Stored Procedures
    ... This is known as ownership chaining. ... Permissions on indirectly referenced objects are not required. ... owned by the same login since database ownership determines the user/login ...
    (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)
  • Re: List Users Permissions down to table.column action
    ... THIS STORED PROCEDURE GENERATES COMMANDS ... -- FIXED PROBLEMS WITH STATEMENT LEVEL PERMISSIONS GRANTING. ... -- CREATE TABLE TO HOLD LIST OF USERS IN CURRENT DATABASE ... -- GRANT USER ACCESS TO SERVER ROLES ...
    (microsoft.public.sqlserver.security)