Re: SELECT permission denied on object

From: Dan Guzman (danguzman@nospam-earthlink.net)
Date: 11/11/02 

From: "Dan Guzman" <danguzman@nospam-earthlink.net>
Date: Mon, 11 Nov 2002 09:52:28 -0600

In order to avoid granting direct permissions to the referenced objects,
the objects must have the same owner. Permissions are checked whenever
the ownership chain is broken. Consequently, a stored procedure needs
to be owned by 'dbo' in order to access a dbo-owned table without direct
permissions.

See Ownership Chains <adminsql.chm::/ad_security_4iyb.htm> in the Books
Online for details. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy  Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------
"Oleg" <pleshtchinskyo@hss.edu> wrote in message
news:98dc01c28996$917aa970$39ef2ecf@TKMSFTNGXA08...
> Hi everyone!
>
> I am having a problem with executing stored procedure...
>
> 1. I have a table created with 'sa' account
> [dbo].[table_name]
>
> 2. I have a stored procedure created with another account
> which is Windows NT integrated account and a member of
> db_owner role.
> [acct_name].[sp_name]
> All this stored procedure does is: 'select * from [dbo].
> [table_name]'
>
> 3. I have another standard account which is a member of
> database public group only. 'public_acct_name'
>
> I granted permissions for stored procedure [acct_name].
> [sp_name] to 'public_acct_name'.
>
> Now, when I try to execute [acct_name].[sp_name]
> using 'public_acct_name' it gives me an error message:
>
> Server: Msg 229, Level 14, State 5, Procedure 'sp_name',
> Line 5
> SELECT permission denied on object '[dbo].[table_name]',
> database 'database_name', owner 'dbo'.
>
> The 'public_acct_name' doesn't have rights to [dbo].
> [table_name].
>
> I thought you can manage security in SQL server by using
> stored procedures and views and not giving direct
> permissions to tables... Now, I am confused. What could
> be wrong? Maybe some server settings?
>
> Thank you for your response.
>
> P.S. I am running SQL server 2000 on Windows 2K server
> machine.
>

Relevant Pages

  • Table record editing permissions
    ... The 'dbo' account means that 'sa' is the owner. ... without being granted permissions. ...
    (microsoft.public.sqlserver.security)
  • Re: stored procedures and permissions
    ... ownership chains and how those work is the part that's ... SQL Server checks permissions on the source ... Server checks permissions on each object where the owner is ... >are executing the stored procedure with the permissions of the stored ...
    (microsoft.public.sqlserver.security)
  • Re: stored procedures and permissions
    ... Suppose I have two developers one with high security clearance ... > permissions to the underlying tables. ... > Server checks permissions on each object where the owner is ... >>It is my understanding that when a user executes a stored procedure they ...
    (microsoft.public.sqlserver.security)
  • Re: Home directory permissions. What to set?
    ... Being Owner of a folder does not confer any access in and of itself. ... that is Owner that account has no access until permissions are changed. ...
    (microsoft.public.windows.server.security)
  • Re: Deny access to all users (including Administrator and DomainAd
    ... permissions on stored procedure override ... applictaion's user account permissions to the stored procedure which will ... Will try to deny access through the query for SQL Server 2005 and 2000. ... Remove everyone that you don't want rom an Administrator Groups ...
    (microsoft.public.sqlserver.security)