If you’ve ever worked through an audit you have probably gotten a request that looks a lot like this:

Please give me a list of all sysadmins.

This is because members of the sysadmin role are super users. They can do anything they want on the instance. But these aren’t the only super users.

Instance Level

sa

This is the administrative login. It can do anything and can’t be stopped. It can however be disabled and it can be re-named. Make sure the password for this account is strong.

sysadmin

The sysadmin role is also really well known. Members of this role have the same permissions as sa. Keeping an eye on the members of this role is a common task for auditing. Remember they can do anything they want to/on your instance. Be very careful about adding members to this role.

control server

The Control Server permission isn’t all that well known in my experience as it’s fairly new. Logins with this permission have almost the same permissions as members of the sysadmin role. There are a few tasks that only sa or a member of sysadmin can do. For example logins with the control server permission cannot add members to the sysadmin role.

This is one other very important difference between granting the permission control server vs adding members to the sysadmin role. Sysadmins cannot be denied any permissions, while control server can. This means that potentially you could grant an administrative permission by granting control server and denying everything you don’t want them to have. I don’t recommend doing this. The permissions granted by control server are vast and it would be very easy to make a mistake by missing a deny, and now your login can do too much or by putting in too many denies, and now your login can’t do what it needs to.

Database Level

dbo

There can be only one! (cheap Highlander reference) The dbo (database owner) is the single owner of the database. By default this is the login that created the database. Think of dbo as a mini sa. Dbo has absolute control over the database. It can’t be disabled (although the associated login can be disabled or even deleted with very little effect on the database itself). It can’t be denied. While I’ve never seen an auditor ask for a list of these users it’s probably still a good idea to keep an eye on who ends up being dbo for your various databases.

db_owner

Members of the db_owner role have basically the same permissions as dbo but can be denied. Much like what I said about Control Server I don’t recommend adding a user as a member of db_owner then trying to deny permissions for much the same reasons. I frequently will see db_owner not treated as carefully as it should (in my not so humble opinion) particularly in development environments. Two simple examples.

• A member of db_owner can delete the database. Yes, delete. As in you had best have a good backup.
• If a database is put in restricted mode no one can log in except administrators. Members of db_owner are administrators. They can log in.

control (database)

The control permission is the database version of control server. Basically it’s the same as db_owner except of course you can’t change who is dbo or add members ot the db_owner role. And again it can be denied. And again you probably shouldn’t try.

Filed under: Auditing, Microsoft SQL Server, Security, SQLServerPedia Syndication Tagged: database permissions, microsoft sql server, security, server permissions