This caused trouble as the user used to execute stored procedure had to be granted execute permission on every stored procedure in the database. This can be automated using scripts that grant execute permissions to the required user. But imagine how much time you'll spend debugging your application after creating a new procedure just to discover that you forgot to run the script.
Another easy and dirty way was to add the user used by the application to the db_owner group and make all procedures owned by the dbo. No need to talk about why this is bad.
In SQL Server 2005, the concept of permissions, securables and schemas ,were introduced.
now you can have it all done using one statement. Assuming you have a user test and a schema named HR, test can have execute permissions on all stored procedures belong to HR schema:
GRANT EXECUTE on SCHEMA::HR to test
Now if you (as dbo) create a procedure like:
CREATE proc HR.GetAccData
as
Select * from dbo.acc
GO
The used test will have execute permission on the HR.GetAccData procedure.
No comments:
Post a Comment