April 7, 2008 at 2:33 pm
I want to create a new login. The user will have access to one database and several stored procedures within it. The stored procedures all return rowsets, querying 4 or 5 tables, but one of the sprocs needs to create a temp table, insert data into, update it based on requirements and then select the massaged data. From a login perspective I'm fine. But what rights should the login have? I can grant select permissions on the tables being used in the sprocs. I can grant execute permissions to the sprocs but what about the temp table? Obviously the user will need create permissions. Anyway, what would be the best approach to keep the user secured but with enough rights to do what they need? I've read a bunch of Brian Kelley's articles but haven't found the right path for me. Any suggestions on the best approach for this? Thanks in advance.
-- You can't be late until you show up.
April 7, 2008 at 2:42 pm
All you need is to grant execute procedures on the stored procedure as long as the stored procedure and tables are all owned by the same owner. For example the sp is dbo.GetCustomers and all the tables are dbo.XXX then stored procedure execute is all you need but if the SP is tosscrosby.GetCustomers and the tables are dbo.XXX then you need Execute permissions and select permissions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 7, 2008 at 2:50 pm
Great, even easier than I thought. I tend to over-analyze and lose sight of what is truly necessary. Thanks Jack.
-- You can't be late until you show up.
April 7, 2008 at 4:55 pm
Piggy-backing on what was already said...
Create a database role and grant it the rights to execute the stored procedures. Make the user a member of that role. Don't assign rights directly to the user. That way if you have to add a second user with similar rights, it's already done.
As has been said, if the owner of the tables/views is the same as the stored procedures, ownership chaining is on. That means execute rights against the stored procedures is all that is necessary. Now, if dynamic SQL is being used, that's a different story. In that case you'll have to give rights against the base tables/views (at least, that's the case in SQL Server 7/2000).
K. Brian Kelley
@kbriankelley
April 7, 2008 at 7:29 pm
K. Brian Kelley (4/7/2008)
Piggy-backing on what was already said...Create a database role and grant it the rights to execute the stored procedures. Make the user a member of that role. Don't assign rights directly to the user. That way if you have to add a second user with similar rights, it's already done.
As has been said, if the owner of the tables/views is the same as the stored procedures, ownership chaining is on. That means execute rights against the stored procedures is all that is necessary. Now, if dynamic SQL is being used, that's a different story. In that case you'll have to give rights against the base tables/views (at least, that's the case in SQL Server 7/2000).
Brian,
For 2005 are you considering Execute As? Because the only time I have tried to use dynamic SQL in an SP in 2005 I needed table rights as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 7, 2008 at 9:23 pm
Yes, for SQL Server 2005 I was thinking of the EXECUTE AS clause as that allows us to get away from assigning rights against the base tables. It means we have to really watch out for the stored procedure CREATE/ALTER scripts since we want to make very sure that such a stored procedure isn't being abused.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply