| Author |
Topic |
|
craigwg
Posting Yak Master
154 Posts |
 Posted - 2011-08-30 : 13:21:08
|
| I am trying to create users of my databases with only the access needed on each ap in an attempt to get away from using the sa account for all applications.The logins and users are all set up. But I get error messages saying that the user doesn't have permission to execute stored procedures. I have researched and found a method to grant execute permissions on all existing SPs, line by line. I COULD do that. But that means in the future when I create more SPs I'll have to grant permission to users for that new SP. While possible, its not realistic.Is there a way to grant exec permission on SPs to a user without making them the database owner? I am hoping I missed something because I swear I have done this in the past.Help!Thanks!Craig Greenwood |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-08-30 : 13:37:17
|
| >>I'll have to grant permission to users for that new SP. While possible, its not realistic.Why not? The GRANT statement is all of 6 words! We always make that part of the create proc code. (after the GO of course)And as far as all the existing procs you can generate the grant statements very easily using information_schema views.Be One with the OptimizerTG |
 |
|
|
craigwg
Posting Yak Master
154 Posts |
Posted - 2011-08-30 : 13:42:50
|
| Because there are many developers and I don't want to design traps in my security. I don't want my developers to start questioning when they create a SP and it then causes the application to fail because they forgot to grant exec permissions to a specific user.Yes it is just 6 words, and that solution would work great for the present. It's the pesky future I'm worried about!Is there a way to simply grant exec to userX on ALL SPs?Craig Greenwood |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-08-30 : 13:46:40
|
| Create database role(s).Add users to the role(s).Grant execute on the procedure to the role and all users in that role will be able to execute the procedure. CODO ERGO SUM |
|
|
|
craigwg
Posting Yak Master
154 Posts |
Posted - 2011-08-30 : 13:52:06
|
| I have the user set up as db_datareader and db_datawriter. I COULD make the user a db_owner, but that defeats my purpose of minimum security I think. If the user was a db_owner he could hypothetically delete things or update things. I guess I need your idea broken down a little bit. I understand I can create database roles. How do I do that?Craig Greenwood |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-08-30 : 14:04:08
|
| You mean is there like a db role (db_spExecuter)? No, there isn't. I think personally think that db_reader and db_writer is also too broad. You should grant permissions only for what is needed and that may not always be every SP in the database to your application user(s). Likewise it probably shouldn't be read/write access to every table. Regarding your risk of breaking applications when you deploy changes - Package up your deployment and run/test it in a testing environment. Then, after successful testing you can confidently deploy it to your production environment. Make it part of you process to include permissioning with all object creation scripts.Books online describes how to create roles and add members. An alternative to that is to create a windows group. Add windows domain accounts to that group. Then add the group as a sql login (and db user). All members of the windows group will now be under that one login's security context.Be One with the OptimizerTG |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-08-30 : 14:05:21
|
| http://msdn.microsoft.com/en-us/library/ms187936.aspx"CREATE ROLE (Transact-SQL)SQL Server 2008 R2"CODO ERGO SUM |
|
|
|
craigwg
Posting Yak Master
154 Posts |
Posted - 2011-08-31 : 11:20:48
|
| As a way of follow up, the decision was made to assign the db_owner role. This errs to the side of simplicity and away from the side of security. Time is an issue so that's what was done. Thanks all for the input!Craig Greenwood |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-08-31 : 11:27:59
|
| Making everyone the owner is no easier or faster than what was suggested above. Bad decision. |
|
|
|
|
Granting permission to users