SQL “Server” runs on top of MS Windows, and it has groups inside of it that are not seen on the Windows server or even the Windows Domain. That’s why we have to check and make sure that inappropriate users don’t have complete access to everything inside the database. Not everyone should be looking at those payroll files!
So, to confuse us a little more, instead of calling them groups (like Users, Administrators, Power Users, etc) SQL calls them “roles.” There are roles based on SQL as a whole (the SQL “Server”) and roles based on individual databases. Kind of like Domain Admins vs Local Admins.
SQL Server roles are equal to Domain Admins inside SQL Server. They give rights to many different core functions of SQL, and the sysadmins role has rights to everything inside SQL. By default, sa is here, but you should look very carefully at any other user in this group. DBAs like to give themselves a “backdoor” into the server this way, but that should be removed. Best practices recommend always having the DBA use their Domain ID for insertion into this role. This way you can log and monitor their access, and when they leave the company and their ID is disabled, they won’t be able to access the SQL databases. It also let’s them know that you are watching, and people tend to behave better that way.
I have found that software developers LOVE to put their names in this group, and not their Domain IDs, either. If it’s a production database, they have no business having anything other than SELECT rights anywhere. Kick ’em out!
Here’s the SQL Server roles default permissions:
Fixed Server Roles
Sysadmin – can perform any activity (and Builtin\Administrators are part of this group by default)
Serveradmin – can set SQL-server config options and shut down SQL Server.
Securityadmin – manage logins and CREATE DATABASE permissions, read error logs and change passwords (within SQL, not Windows)
Setupadmin – manages linked servers and startup procedures
Processadmin – can manage processes running in SQL server
Dbcreator- can create alter and drop databases
Diskadmin – can manage disk files
Bulkadmin – can execute BULK INSERT statements
How do you find out who is in these roles? Use the results of a stored procedure: sp_helpsrvrolemember with the results taken from the Master database.
For individual databases, there are database roles. People in these roles can be all powerful, but only within that individual database, not all of SQL. How do you know how many databases are inside one SQL Server installation? Again, a stored procedure: sp_helpdb
You’d be surprised how DBAs like to create their own little mini databases (on production boxes!) just to “do things.” They may be great “things,” but those databases need to go somewhere else.
In order to see the members of all the database roles, run another stored procedure: sp_helprolemember run from each production database in SQL Server. It’s a little tedious, but you will get the information you need.
I commonly monitor the membership in the db_owner role (equivalent to a local server Administrator). By default, the dbo, or db_owner is the only one in this role. If the DBAs are already sysadmins, they don’t need to be in this role, unless you want to be very granular in your controls (NOT a bad idea). Developers shouldn’t be in this role, or just as bad, the application ID of an application accessing data.
This is where a lot of software development falls off the security bandwagon. If the application ID is db_owner, it means that the ID has access to everything in that database. If I can acquire the application ID’s password, (and some of them don’t have one) I can get into all the data. I wouldn’t use the application, just connect directly via ODBC or even Excel.
It’s easier to write applications using the application ID as db_owner, but unless you are using middleware to vet everyone’s login via the Windows Domain, you take the risk of losing all that confidential data out the back door.
If you can acquire this information on a quarterly basis, you will go a long way towards having an easy audit and a better night’s sleep!