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 |
|
jholovacs
Posting Yak Master
163 Posts |
 Posted - 2008-12-04 : 12:06:19
|
I have a server (in production, God help me) that supports approximately 30 web applications with over 200 SQL logins, each designated for a specific role or function. Originally, it would appear that these logins were fairly well restricted to get the access they required and little more, but over time, feature creep has led to security creep, and a lot of these accounts have their fingers in a lot of pies that they really don't need. It also appears that at some point the object-level access granted to individual accounts was abandoned in favor of database-level permissions. There are approximately 45 databases, ranging in size from 100 MB to 100 GB, and numbers of objects ranging from a couple of hundred to tens of thousands.Due to some rather machiavellian HR shell games, I have officially inherited this server, and have been asked to "secure" it.One of the things I'd like to do is create application accounts instead of these "function" accounts. With our source control system, I can FAIRLY easily come up with a list of procedures and functions that each application calls. I would like to take this list and compile a cross-database object-level "required permissions" report, and assign the permissions to the application user accounts per that report.Does anyone know of an effective tool to do this? MS dependency tracker can give me object it depends on, but I'm looking for the permission types as well, i.e., EXECUTE on dbo.proc_get_widgets requires SELECT on dbo.widgets, SELECT on dbo.widget_properties, INSERT on dbo.widget_log kind of thing.Any input is welcome.
SELECT TOP 1 w.[name]FROM dbo.women wINNER JOIN dbo.inlaws i ON i.inlaw_id = w.parent_idWHERE i.net_worth > 10000000 AND i.status IN ('dead', 'dying') AND w.husband_id IS NULLORDER BY w.hotness_factor DESC |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-04 : 13:04:02
|
| What about this one? It won't give you exactly but you can interpret from it.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111918 |
 |
|
|
jholovacs
Posting Yak Master
163 Posts |
Posted - 2008-12-04 : 14:03:12
|
Well, what I really need is a recommendation for a third-party tool, I think. I am trying to decide what these accounts need based on what they do, not what they have based on what somebody gave them. One good thing about the managed code connecting to this server is everything is run thru stored procs and functions; there are no dynamic SQL that I have to interpret permissions for.
SELECT TOP 1 w.[name]FROM dbo.women wINNER JOIN dbo.inlaws i ON i.inlaw_id = w.parent_idWHERE i.net_worth > 10000000 AND i.status IN ('dead', 'dying') AND w.husband_id IS NULLORDER BY w.hotness_factor DESC |
|
|
|
|
|
|
|
|
SQL Server object dependencies