Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shooksm
Starting Member
2 Posts |
 Posted - 2002-11-06 : 10:10:51
|
| Is there an easy way to grant execute rights to all user created stored procedures in a particular database to a particular user without having to individualy grant rights to each object or use EM to do it? I am looking for something similar to the lines of the roles like db_datareader and db_datawriter in that the user is simply assigned to the role and given instant access to SELECT, INSERT, UPDATE and DELETE. In this case I am looking to grant EXECUTE permission on all user created procedures to a specific non dbo user account.Here is a little background regarding my question:I am an intranet application developer and I am trying to get some buyin for the other developers to use stricly stored procedures for data access and manipulation. They are used to creating a database and it's tables, adding the generic web user account as a user of the database, then assigning them to the db_datareader and db_datawriter (or sometimes even db_owner) roles. Then they don't have to worry about the individual permissions on the tables they create. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-06 : 10:18:43
|
| No they wouldn't, however they should be worried about the ability of that user account to DELETE rows at will, and even TRUNCATE TABLE if they are members of db_owner (not to mention DROP TABLE and all that). And if you or the developers are not aware of SQL injection attacks, you should definitely read up on it: http://www.sqlteam.com/SearchResults.asp?SearchTerms=injectionNot using stored procedures is a great way to get bombed by SQL injection.You can always create a template file for your stored procedures that includes the appropriate GRANT EXECUTE <sp_name> TO <user> at the bottom of the code; you'd only have to change the name of the procedure each time you create a new one. |
 |
|
|
|
|
|
Stored Procedure Security