Error# -2147217911 Select Permission Denied ..

greenspun.com : LUSENET : SQL Server Database Administration : One Thread

One of our users is getting the following error: Error# -2147217911 Select Permission Denied on object , database , owner

This error occurs when the user tries to run a stored procedure (SP). This user has execute permission on SP. Although I have granted select etc. on underlying table the user is still getting the same error.

(I think that I do not need to grant select etc. on underlying table which the SP is using as long as SP has exe permission.)

Any suggestions... Thx,

-- Anonymous, December 19, 2001

Answers

Manoj,

It sounds like you may be having problems with tangled "ownership chains". There is an article in SQL Server Books Online (for SQL 2000) titled, "Using Ownership Chains" that explains it a bit. (In SQL 7.0 Books Online, search for ownership chains.)

The article says: "When a user accesses a view, Microsoft® SQL Server™ does not check permissions on any of the view's underlying objects if these objects and the view are all owned by the same user, and if the view and all its underlying objects are in the same database. If the same user owns a stored procedure and all the views or tables it references, and if the procedure and objects are all in the same database, SQL Server checks only the permissions on the procedure.

If the ownership chain of a procedure or view is broken (not all the objects in the chain are owned by the same user), SQL Server checks permissions on each object in the chain whose next lower link is owned by a different user. In this way, SQL Server allows the owner of the original data to retain control over its accessibility.

Usually, a user who creates a view has to grant permissions only on that view. For example, Mary has created a view called auview1 on the authors table, which she also owns. If Mary grants Sue permission to use auview1, SQL Server allows Sue access to it without checking permissions on authors."

In practical terms, this means make sure that stored procedures are created by sa. Then you will not get tangled ownership chains.

Hope this helps,

Eric

-- Anonymous, December 20, 2001


Moderation questions? read the FAQ