execute permission for stored procedures

Hello SQL Experts,

we've got a Windows Server 2003 environment with SQL Server 2000 Sp 3.

A stored procedure selects specific data from a user-table which depend on the user executing it. The users are granted execute permission on the stored procedure. But execution fails, if the user is not granted select permission on the user-table, too.

The problem is, that the user must not have the permission on all data in the user-table but on the data concerning him.

In earlier versions of SQL Server and Windows the execute permission has granted sufficient rights to select from the underlying tables. How can this be re-established?

The Owner of sp and table is dbo.

Thanks for your replies!You can't enable the user to only have permission to some of the data in the table. What you would need to do is have a view, and a column by which you would differentiate between different users. There is little cost associated with a view, and you can make the view owned by the user, so no one else can use it, or no one else can use it by mistake.

IE. [user].[viewname]

Cheers,
-Kilka|||While you can restrict access to columns using permissions, I don't know of any way to restrict access to rows using permissions.

I think that Kilka101 has the right idea about using a view. You may be able to construct a single view that uses User_Id() or Suser_Sname(), or you may need to resort to separate views for each user.

Keep in mind that as you scale upward, this gets a lot more complicated to manage, especially if you introduce any "third tier" processing like an application server. For two-tier applications this isn't likely to be a problem, but as you grow it can become a real problem.

-PatP|||Thanks for your replies!

I thought about a workaround with views, too. But I'm rather sure to remember that before Windows 2003 and SQL Sp3 it has been sufficient to grant a user the right to execute a procedure which performs a select on a table without having the explicit right to select from the table.

For example:

create proc sp_showusers
as
select * from users
where name= 'abc'
go

grant execute on sp_showusers to [my_user]
go

Though 'my_user' doesn't have granted the select permission on table users, he's got all data from table users by executing the sp.

I suppose it has something to do with the stricter security settings coming up with Windows 2003. Anyway, there must be a way out of it... :confused:|||Yes, when you create a stored procedure, the statements in it are checked against the security permissions of the creator. Permission to execute the procedure can be given to users with different permissions, that can then execute the procedure even though it does things such as your example select that the user couldn't do directly. Note that dynamic SQL is an exception to this observation, the executing user must have permission to execute any dynamic SQL.

-PatP|||Note that dynamic SQL is an exception to this observation, the executing user must have permission to execute any dynamic SQL.

Oh... okay, I guess this is exactly the point! The stored procedure I got the problems with contains some dynamic SQL... :( So I have to think about views then. ;)

Thank you very much for your help! :)