System Administrator server role

[bessebo]

(We are running SQLServer 2000, service pack 3)

We recently had some Sarbanes-Oxley auditors in to check out our systems. It was suggested that we never routinely use the sa account when not needed. So what I did was set up a login called "Admin" and gave that user the System Administrator server role.

My question is about the databases that are on that server. If you look at the database access on the sa account the sa account is part of the public and the db_owner database roles for all databases on this server.

For the new login "Admin" that I created do I need to make that user part of the db_owner database role for each database as well as giving it the system administrator server role so that all of the privileges of the "Admin" login are equal to the "sa" login?

Thanks,
Bessebo

 

[SPV]

In my view, and probably in the view of your auditors too, what you should do is log in with the minimum level of permissions required to do the job you need to do.

Try leaving out all the database-specific permissions to start with and see how you get on. If you need to grant them, start with the lower level permissions first (public) and only move up the levels (towards db_owner) if things don't work.

Ideally you would create more than one account, for different purposes. Then you connect with the least privileged account normally, only using the more privileged accounts when necessary.

 

[mrdenny]

Creating an Admin account with the same rights as the sa account is the same as using the sa account. Each admin should have a seperate account so that when that person leaves the company there account can be removed, and so that all changes to the system can be tracked back to the person that made the changes.

Having two accounts for each admin is a good idea, however I usually always need admin rights on the server, as 99% of the time I'm doing config work, or other work that needs admin rights so I almost never use a non-sysadmin account.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[bessebo]

Thanks for the posts. Mr Denny, you really did not answer the question. I guess what I need to know is if I give the System Administrator role to a login is there any need to make this user a db_owner of the databases on that server. I did some testing in our test environment yesterday and it appears that if this login has the System Administrator role that I can pertty much do anything like delete from any table and create stored procedures. It appears that giving db_owner is superfluous. Any comments?

Thanks,
Bessebo

 

[mrdenny]

Sorry about that.

If a login is a member of the sysadmin role then they can do anything on the server that they want. There is no need to be a member of the db_owners role.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[bessebo]

Thought so because during my testing I could do anything if I was in the System Administrators role.

Thanks for your response.

Regards,
Bessebo

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog