News Stay informed about the latest enterprise technology news and product updates.

SQL Server security tips: part 1

From removing guest accounts to avoiding dynamic SQL, security expert Greg Robidoux has tips for making SQL Server bulletproof.

Greg Robidoux, a Microsoft SQL Server expert who focuses on security, is chairman of the Professional Association for SQL Server DBA Special Interest Group (PASS DBA SIG). He is also a expert and the founder of Edgewood Solutions, a Merrimack, N.H.-based database consultancy that focuses on Microsoft SQL Server.

Robidoux said he regularly sees security concerns pushed to the bottom of priority lists.

"Usually, there is a time crunch to get an application to production, and security is not even addressed," he said. "DBAs especially need to push ahead and say that security does need to be an important concern."

In an interview with, Robidoux laid out 10 technical tips that can help any SQL Server administrator avoid security leaks. Here are five of them.

1. Don't use the system administrator account.

Use of the SA account masks the identity of the real user accessing SQL Server, so it becomes harder to track who is making changes or who is accessing the server. Moreover, if a weak password is used, or if there is no password, this is the first place hackers look when trying to gain access to the server. Create accounts tied to domain accounts and use trusted connections to access the database server. Use the system administrators' role to grant system admin rights to the databases. Create a strong SA password and lock the password away for emergency situations.

2. Give users only the rights they need to do their jobs.

When there is a limited amount of time to research a problem, the first thing usually done is to grant users more access. This usually fixes the problem, but it also opens up security holes. Try to standardize on user-defined database roles, and grant sufficient rights to these roles instead of granting them directly to logins. Also, never use server roles for regular users. If there is a need to grant an individual extra rights, make sure the reason is noted and include what was done (so access can be revoked at a given time), or roll these changes into the user-defined database role.

3.Remove BUILTINAdministrators group.

The BUILTINAdministrators group gives anyone with local admin rights on the server full control over the databases. This account should be dropped from SQL Server to gain better control over who has access. Create a new domain group for the database administrators, add the DBAs to the group and add this group to the database. The new group should have access to the system administrators' role and then the BUILTINAdministrators group can be dropped. Then there is better control over which users have system admin access to the databases by adding and removing people from this group.

4. Remove GUEST account.

The guest account is used to provide access to a database for a login that has not been made a database user. A login without its own database permissions will obtain permissions granted to the GUEST account. Drop the guest account from all of your databases. The only exception to this is Master and TempDB, where this account cannot be removed.

5. Shut down and disable any unneeded services.

There are a lot of services that are bundled with Windows and other applications. Having additional services running on the database server that are not required creates additional security holes in the databases. Try to dedicate a server for SQL Server. This allows the running of minimal services that Windows needs, as well as the services that SQL Server needs. When security alerts are posted, the scope of what should be read can now be limited, so you don't feel obliged to read all of them or, worse, ignore all of them.


Read tomorrow for the conclusion of Greg Robidoux's Top 10 SQL Security tips.

Check out our featured topic on your SQL questions answered.

To provide feedback on this article, contact Robert Westervelt.

Dig Deeper on SQL Server Security

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.