Audit and Document SQL Server Roles

This article discusses how to audit and document SQL Server roles with the SQL Server administration tool, SQLDocKit.

We’re going to review why documenting logins and roles is important.

SQL Server Security

It’s a good time to be interested in SQL Server and Database roles auditing. Now of all times, when attacks on enterprise data are on the rise.

There’s no doubt it will happen—it’s rather the question of when. That’s why most DBAs working for large corporations are oriented on prevention.

Quickly identifying breaches and potential threats is the best way to secure corporate data. To do this, you have to know who’s accessing your SQL servers and databases as well as be able to differentiate which users really have the need to view which data.

DBAs must ensure that elevated permissions are not assigned to users who have no need for them, especially when assigning SQL permissions can quickly get out of hand. You shouldn’t allow just anyone to snoop around your servers and databases.

Are you aware who has access to your servers?

The truth is that most scenarios involve “silent” security breaches that you don’t even notice until it’s too late. Data gets stolen, piece by piece, minus the big fuss you see in the movies. It could be stolen by a former employee whose account and login credentials someone forgot to disable. Or when someone is accidentally added to the sysadmin role. Or when, somehow, everyone suddenly has access to all servers in the environment. The devil is in the details, indeed.
If no one audits SQL Server accesses and permissions, it can sometimes take months before an attack is noticed. By that time, the whole corporate network is compromised.

What should you check?

SQLDocKit can help you with the following list of checkups to make sure you fully secure your SQL Server environment.

  • Make sure that the SA account doesn’t have blank passwords or simply the same SA password. This is a huge security issue—you wouldn’t believe how many SA users have blank or simple passwords.
  • If you’re not required to use SQL authentication, don’t. For example, cross-domain authentication without trust, etc.
  • Check that you are not using same service account for different SQL Server services.
  • Confirm that the sysadmin hasn’t granted the sysadmin role to the domain user’s security group. (It’s ridiculous how many times these things have happened.)
  • Make sure that the Windows Server that the SQL server is sitting on isn’t accessible to unauthorized users. For example, domain users are part of the local admins on the Windows Server.
  • Know who has access to your database backups.

If databases are not encrypted, it’s basically like handing users the whole server environment and all corporate data on a silver platter. No one, except the DBA, should have access to the backups. Sure, accidents happen. And, yes, users might not be able to modify the data stored in those backups. However, they can mount the database somewhere else and get insights to everything.

Document Security Changes

SQL Server has server roles to help you manage permissions on a server. Basically, server roles are security principals that group other principals, where these roles are like groups in Windows OS. Fixed server roles have a fixed set of permissions while logins must be mapped to the database for a user to reach a database.

The thing with permissions, however, is that granted permissions for both the fixed server and fixed database roles can’t be changed.

SQLDocKit offers a detailed SQL Server inventory of fixed and assigned roles on all SQL servers and databases in your domains.

With this report, you can list all SQL Server logins, databases, and roles.

You can also see who can change the data on your servers and list:

All principals that are members of the sysadmin role

All members of the db_owner role

By having the entire list as one report, you can cross-reference who has which permissions and on which servers in your domain. Also, you get a map of logins and users with their corresponding roles at the server and database level.

With SQLDocKit, you can audit SQL servers and databases hosted in Azure as well.

Fire up SQLDocKit with a 30-day free trial, and navigate to SQL Inventory SQL Servers Database Engines Logins & Roles. You can export it, print it out, and have it ready for your next board meeting.

Assigning and removing permissions with SQLDocKit

Security management tasks can be done across multiple SQL servers from a single UI. With the new Security Management feature, you can manage server-level permissions.

Use server management actions to successfully manage your SQL Server permissions by:

  • Choosing principals to whom you want to grant server roles on selected SQL servers. If a login doesn’t exist on any of the selected servers, a new one will be created.
  • Removing logins from assigned SQL Server roles.
  • Deleting SQL Server logins. This prevents access to an instance of the SQL Server under that login name.
Select server role(s) you wish to revoke for principal(s).

Select server role(s) you wish to revoke for principal(s).

Track security changes

The SQL Server inventory is contained in the form of a snapshot. This means you can select two snapshots and compare them to check for changes in the SQL environment over a certain time.

Give SQLDocKit a try. Life is complicated enough as it is. So why not have SQLDocKit do the administration for you?