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 |
|
nu_dba
Starting Member
8 Posts |
 Posted - 2006-03-02 : 11:46:16
|
| I am in the process of locking down the SQL Server in an environment that is considered to be in production (pilot stages) and there is no staging or test environment that mirrors it. I need assistance in determining the server and database roles to assign to existing logins, most of which currently have sa and dbowner rights. Because it is not a development environment, I need to be sure that downgrading the server and/or database level permissions will not break any functionality. I'm starting with the logins that have the SA fixed server role. These logins need to be able to install applications that require the use of a backend database, which will be stored on SQL Server. In addition, through the installation process a new login/password for the newly created database(s) is normally created. For the existing logins with the SA fixed server role, will downgrading to the securityadmin and dbcreator roles be sufficient to facilitate those needs, or are those too much/ too little? And should any user account ever be granted the SA role? If so, what questions could I ask to determine this need?Since these install process for these applications usually prompt to install using SA or local system account to authenticate to SQL to create the new database(s), that account should have securityadmin and dbcreator roles to create the database and its tables, as well as add a new login to that database. Please address this question, keeping in mind that the logins will only be performing the described actions, installing apps using SQL Server as the backend database and adding a login to that database (which may or may not be done during the installation process).Thank you,nu_dba |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-03-02 : 22:49:28
|
| 1. make an assessment, gather data, ask questions from those involved2. plan it out, verify those missing info from #1 like server roles, DBA tasks, etc3. then map those info in #1 and #2 to the definitions of server and database fixed roles, refer to BOL for details of these roles4. get feedback from those involved of your prosposal, they should know what are the minimum permissions that their logins need5. know how to compromise at this point, no use in securing the environment if they won't cooperate and using brute force6. for those apps that prompt accounts for installation, ask what are the new accounts that it will create, permissions, etc, after installation, remove the accounts that are not neededpretty generic but HTH--------------------keeping it simple... |
 |
|
|
|
|
|
|
|
How to decide on Server Roles and DB Roles