Over the last few years, I have been a roving SQL Server DBA contractor, tending to work contacts in small and mid-size companies that involved organizing, documenting and then tuning/optimizing existing SQL Servers in those companies. I have noticed one very disturbing truth over the years; no one seems to document anything. In fact, I was usually thankful if I could find something or someone who knew the SA passwords on each SQL Server installation, let alone know anything about their setups.
I have often been asked how I could go into a company that had dozens of servers, over 50 SQL Server databases, no existing documentation, no full-time DBA on staff, and no documentation, and ramp up to a functioning level in a very short time frame. My answer was practice, and my file of stored procedures, written over the years, that I carry with me that allows me to do quick audits of the SQL Server installations and databases so I can quickly produce documentation to work from.
This article is the second article in a series to share those stored procedures and methods I have learned with you, in order to help you document and learn a new environment if you move on, or obtain another project at your existing company.
Overview of SQL Server Roles
In response to reader requests, I’m going to go into a little background on SQL Server roles before I start describing the auditing process. SQL Server roles, either fixed or user-defined, are SQL Server’s answer to Windows groups, and basically serve the same purpose. Roles are a way to collect a batch of users and define the same set of permissions for those users without granting permissions to each individual user. Having said that, remember that if you get into the habit of granting permissions to roles rather than individual SQL Server logins, you will have a much easier time maintaining permissions in a dynamic environment.
SQL Server ships with a selection of fixed server and fixed database roles that should be at the top of your list to examine when you decide to grant permissions to roles rather than individual logins.
Fixed Server Roles
System Administrators (sysadmin) This role is all encompassing and can do anything in SQL Server without setting any object permission. Use of this role should be highly protected and not assigned to logins without extreme justification.
Server Administrators (serveradmin) This role is used to set server-wide configuration options and shut down servers. The logins assigned to this role also have the ability to add other logins to this role.
Setup Administrators (setupdamin) This role can manage linked servers and all startup procedures. The logins assigned to this role also have the ability to add other logins to this role.
Security Administrators (securityadmin) This role can manage logins and CREATE DATABASE permission, read error logs, and change passwords. The logins assigned to this role also have the ability to add other logins to this role.
Process Administrators (processadmin) This role can manage processes running in SQL Server. The logins assigned to this role also have the ability to add other logins to this role.
Database Creators (dbcreator) This role can create, alter, and drop databases. The logins assigned to this role also have the ability to add other logins to this role.
Disk Administrators (diskadmin) This role can manage disk files. The logins assigned to this role also have the ability to add other logins to this role.
Bulk Administrators (bulkadmin) This role can execute BULK INSERT statements. The logins assigned to this role also have the ability to add other logins to this role.
Fixed Database Roles
Database Owner (db_owner) This database role is all encompassing and has all permissions in the database.
Database Access Administrators (db_accessadmin) This role can add or remove Windows groups or SQL Server users to the database.
Database Security Administrators (db_securityadmin) This role can manage all permissions, object ownership, roles, and role memberships.
Database DDL Administrators (db_ddladmin) This role can execute all data definition language (DDL) statements but cannot issue GRANT, REVOKE or DENY statements.
Database Backup Operators (db_backupoperator) This role can execute DBCC, CHECKPOINT, and BACKUP statements.
Database Data Reader (db_datareader) This role can SELECT any data in any user table in the database.
Database Data Writer (db_datawriter) This role can INSERT, UPDATE, or DELETE any data in any user table in the database.
Database Deny Data Reader (db_denydatareader) This role cannot SELECT any data in any user table in the database.
Database Deny Data Writer (db_denydatawriter) This role cannot INSERT, UPDATE, or DELETE any data in any user table in the database.
Public (public) This is a special role in which every database user belongs and is primarily used by SQL Server to give permissions to users to use objects needed by SQL Server to authenticate the user, let the user use Enterprise Manager, etc.