SELECT permission denied on object

From: Oleg (pleshtchinskyo@hss.edu)
Date: 11/11/02 

From: "Oleg" <pleshtchinskyo@hss.edu>
Date: Mon, 11 Nov 2002 07:25:29 -0800

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

  • 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)
  • Re: login password changed
    ... That refers to the DTS method, not the stored procedure method. ... need to edit the file so you get the correct machines name in the account name. ... Tibor Karaszi, SQL Server MVP ... If you have to transfer logins to an instance of SQL Server in a ...
    (microsoft.public.sqlserver.server)
  • Re: Set perissions on stored procedure
    ... stored procedure for one of my database roles, ... stored procedure and choosing All Tasks -> Manage Permissions. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • Re: Stored procedure loses permissions daily
    ... Columnist, SQL Server Professional ... Senior Database Administrator ... > I have a stored procedure in a SQL 2000 database that loses all of the ... permissions back to what it should be -- very frustrating. ...
    (microsoft.public.sqlserver.security)
  • Re: SELECT permission denied on object
    ... In order to avoid granting direct permissions to the referenced objects, ... I have a stored procedure created with another account ... > database 'database_name', owner 'dbo'. ...
    (microsoft.public.sqlserver.security)