| Author |
Topic |
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
 Posted - 2004-12-16 : 17:14:44
|
Ok after taking my new assignment I now have 31 servers and 200+ NT/Sql accounts getting access to 100+ databases, Great... Wow... Hmmm, UhhhOkNow that I have Deleted 50+ accounts that have not been in use and modified over 20+ for improper levels of permissions, I am looking at the long term management of these rights I am implementing NT Groups and squezing the hell out of the sql accounts.Now I am looking at my permissions for my development team... Now at DB Owner in every environment Development, QA, and Production. I have identified those within the group that can be trusted for backups and restores as well as security administrators for Development.How can I easily clamp down on my Staging and Production environments for these "special" users without allowing them to create new objects in these environments. Please do not tell me to assign execute permissions to every sp in every database!I have reviewed the followingCreating a Manageable Security Plan:[url]http://www.windowsitpro.com/SQLServer/Articles/ArticleID/15446/pg/2/2.html[/url]Signed very sick and tired of working the security cleanup!!!   |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-16 : 17:26:35
|
| Here's what we do:Development - developers have db_ownerQA - QA team has read/write and developers have read and when necessary writeProduction - developers get read permissions onlyTara |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2004-12-16 : 19:58:08
|
| Yea I read that post on Tuesday... Which tells me I have to assign execute permissions to each sp individually for every user/NT group/Sql Account... I was hoping for a better approach but if one does not exist then it does not exist and I'll stop whining and implement. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-16 : 20:05:48
|
| Well can't you just create a role, add the users to the role, then grant the permissions to the role?Tara |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2004-12-17 : 00:12:24
|
| I like that approach in concept... Just to verify Unlike the fixed roles "data reader" "data writer" their is no way to configure the fixed permissions to allow execute on all sp's in a db without granting dbowner.All of our developers have done intensive effort on creating methods for their applications to only execute using sp's. Great Job. However how do I restrict the 200+ NT users from deciding its a good idea to execute directly against the database.I find it odd that you SQL does not have any way to grant execution to all sp's without giving away the store... Sorry to say but maybe this is a piss pore implementation on SQL's Fixed role options. But maybe that is why I have a job! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-17 : 00:21:05
|
The thing is, if they did something like that, they'd have 75 or more built-in database roles, and I can see far more instances where I don't want a role to have that kind of power either. I already have problems like that with db_datareader and db_datawriter. It's always more secure, and usually a lot easier, to build up from nothing than to try and take away from everything.Besides, using a role for this is a great idea (we do something like it too) and you've got the code generating script already...it'll take you longer to whine about it than to actually do it.  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-17 : 12:08:50
|
| I don't see why you can't just generate the code to grant the permissions. If you need more code examples of this, search the forums for my stored procedure named isp_Grant_Permissions.Tara |
|
|
|
|
New Security Plan