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 |
|
tfountain
Constraint Violating Yak Guru
491 Posts |
 Posted - 2008-05-29 : 09:23:41
|
| Question - if you had to completely strip all permissions from all databases in an instance and reset them, assuming you have metadata to support rebuilding the permissions, what steps would you follow? I can handle the iterating through each database, but at the database level, what steps would you take?The reason I inquire on this is I currently have a job that I inherited that does just this. But it's buggy and was also written in for SQL Server 2000. With some of the changes in 2005, a few bugs have crept in, etc. And I would like to confirm my thoughts this. Or, if your opinion is why are you wasting your time on this? Then that's fine to and I'll review any constructive comments you may have. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-29 : 13:16:48
|
| What is the point of this?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
 |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-05-29 : 16:28:13
|
quote:
Originally posted by tkizer
What is the point of this?
Oh, leave it to the first poster to ask 'why'!! :).We do this for several reasons but I can summarize it by saying if we did not have this metadata (controlled by the DBA group) then over a period of time, every account would be a sysadmin. I don't like that personally. But aside from that, any suggestions on (a) removing all permissions and (b) restoring all permissions (assuming there is data stored to rebuild)? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-29 : 16:42:21
|
| Why do people have the permissions to grant sysadmin? Why isn't the environment locked down so that the DBA group controls security?There is no easy way to do it. I can think of two ways:1. Drop the users from the database, then re-add them and run your script2. Loops through all users, for each user loop through all objects and remove permissions, then run your scriptTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-06-05 : 16:29:40
|
quote:
Originally posted by tkizer
Why do people have the permissions to grant sysadmin? Why isn't the environment locked down so that the DBA group controls security?
Tara, thanks for the response again and sorry for the delayed response. I've been sidetracked on another project.Anyway, for the most part we do this, but with sparing you the history of our shop lets just say it was not always possible to do this. Even today we run a small risk until I am able to clean up some of the issues we have with permissions being broader than they should. As a safeguard we have a process currently written that performs the following tasks (written years ago by another DBA no long with our organization):A) Revokes all server wide permissions except CONNECT SQL - don't know why.B) Grants server wide permissions. Only ALTER TRACE and VIEW SERVER STATE are supported currently but I want to extend this.C) Assigns database users to the role SQLAgentOperatorRole (in msdb). Creates the database user if it does not exist first.D) Removes unknown users from the role SQLAgentOperatorRole and drops the user from msdb.E) Verifies all logins exist and if any are found not in the metadata they are dropped. This step also verifies any user that should be a sysadmin is and vice versa.F) Validates no database object is owned by any owner (remember, written for SQL Server 2000!). If any are found the owner is changed.G) Grants database level permissions for logins across all databases except master, msdb, model, tempdb, Northwind, Pubs.H) Drops orphaned users in each database.I) Adds any database users that are missing to each database.J) Creates any missing custom roles across all databases.K) Assigns objects to custom roles based on naming standards.L) Revokes unauthorized permissions to database objects (based on metadata)M) Removes. accounts from roles they should not be in (based on metadata).N) Removes unrecognized roles.O) Adds necessary accounts to custom and database roles.P) Removes unknown loginsQ) Adds / removes server roles from loginsAfter all this there is an entire section on 'special cases' lol.Ok, with this in front of you (and your head probably twisting like that kid on the Exorcist)... this is what I'm dealing with. First, I personally think this one process does way to much and the logic can be broken out. Also note that there are 'shop' reasons as to why we do this but I'll go over those later. I'm thinking of a more simple approach:A) Drop all SQL Server logins (except sa)B) Cycle all databases and all objects and validate only known schemas own objects (currently dbo only).C) Cycle all databases and drop all users, schemas (except dbo, guest obviously).D) Cycle all databases and drop all custom database rolesE) Cycle all databases and create only known custom database roles (can be different per database)F) Cycle all databases and relate objects to known custom database rolesH) Create all known SQL Server logins basedI) Grant all server wide permissions based to known SQL Server logins.J) Cycle all databases and assign database roles to SQL Server logins.K) Cycle all databases and assign custom database roles to SQL Server logins.Right now the process is written as one huge stored procedure but I would like to break it out into logical units and have the ability to run any of these as needed with our nightly job still doing this as-is.Whew...ok...thoughts? |
|
|
|
|
|
|
|
|
Stripping and resetting security