I thought that if a user account had execute permissions on a stored procedure they would not need permissions on the underlying tables the stored procedures access. This way the users could only manipulate data in a way proscribed by the stored procedures. However I have a situation where I have had to give the user account select and delete permissions on the underlying tables in order for the stored procedures to execute. Do you know if there is a way to correct this problem?

Info: SQL Server 7.0 SP2. the user account is a standard security account with a user role in the database being used. The database user is not attached to any roles( it is an account set to be used by a MTS component using ADO). The user has execute permissions on all of the stored procedures ( and now on the tables).

Any clues are appreciated. Thank you, Paul Tribe

-- Anonymous, May 25, 2000

Answers

Paul,

I suspect you have a broken ownership chain. The SQL Server Books Online article, "Ownership Chains" describes the ownership chain concept.

Typically, the owner of a stored procedure also owns all the procedures, tables, and views referenced. When a user calls a stored procedure, SQL Server does not check permissions on any of the underlying objects if they are owned by the same user (as the stored procedure owner).

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 who is authorized to access it.

Typically, as in your case, this is not desired. You can avoid this by having all objects owned by dbo. You can acheive this by aliasing (as dbo) all the users that will be creating objects.

Hope this helps,

Eric

-- Anonymous, May 25, 2000