Granting Execute permission to user by dbowner not working

Question

Granting Execute permission to user by dbowner not working

Harry Nath

SSC Veteran

Points: 217
More actions
July 24, 2005 at 11:29 pm

#90279

Here is the scenario:
There are two logins, owner1 in the db owner role, and user1 in public.
owner1 creates a simple SELECT stored procedure (say, usp_get_data) and grants Execute permission on that stored procedure to user1.
Using Query Analyzer, I login to the database as user1 and run:
exec owner1.usp_get_data
This results in a message that says user1 does not have Select permission to the underlying table that is being queryied by the stored procedure.
That makes no sense to me. The owner has granted execute permission to the user. The user should not need permissions to the underlying table.
Any ideas? What am I doing wrong?
Harry Nath

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

Is the table owned by owner1 or some other user (like user2)? It sounds like it is owned by user2, and in that case the ownership chain would be broken and therefore user1 would need SELECT permissions on the table.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Harry Nath SSC Veteran Points: 217 More actions · Answer 2

Chris,

owner1 is in the role of database owner.

I recalled something else and I'm not sure whether this is relevent. The database was moved from test to production by doing a backup and restore. In test, owner1 (myself) also had Sytem Administration priviledges. So, all stored procedures created under owner1 had the prefix "dbo", as is "dbo.usp_get_data". In production, when the stored procedure was created, it had the prefix "owner1", as in "owner1.usp_get_data". As the other stored procedures were in production by restoring the database, their prefixes have not changed and everything is working as expected.

Does the stored procedure have to be created by someone with sa priviledges? It seems to me that the database owner should be able to create stored procedures and grant access to them independently of the sys admin.

Thanks.

Harry Nath

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

Does User1 have read permissions for the database ?

DCPeterson SSCoach Points: 15266 More actions · Answer 4

It all depends on who owns the table(s) being referenced by the stored procedure. If you have dbo.table1 being referenced by the owner1.usp_get_data proc this creates an Ownership Chain. Regardless of who is granted execute rights to the proc, since owner1 doesn't own the table, permissions on the underlying table are not assumed and must be explicit. However, if owner1 owns both the table and proc, it is assumed that since user1 is granted execute permission by owner1 to execute the proc, owner1 must have intended to grant access to the table(s) it references.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

Harry Nath SSC Veteran Points: 217 More actions · Answer 5

The table was created by dbo and is thus owned by dbo. However, owner1 is in the Database Owner role and thus should own *all* the objects in the database. If owner1 grants access to execute a stored procedure to user1, that should work. User1 should not need read access to the underlying tables. One purpose of having stored procedures is to have a layer of security. If I give user1 access to the underlying tables, I am defeating the purpose. Or am I missing something?

Thanks.

DCPeterson SSCoach Points: 15266 More actions · Answer 6

You are certainly missing something.

The db_dbOwner group does not give actual ownership of the database nor of any particular objects to its members. It does give most ownership rights so that a member of the db owner group can drop, modify, or grant permissions on any objects. This is different than if you are logged in as sa or a system administrator. If that is the case you are automatically aliased to dbo (as opposed to a member of db_dbowner group).

All that aside, even if you are sa and create a owner1.procName that selects data from dbo.tableName and grant execute to user1, if user1 doesn't have explicit SELECT rights to dbo.tableName (either through a GRANT or by group membership) user1 will be the proud recipient of an error when he or she attempts to run the proc.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/