SQL Server security

来源：这里教程网时间：2026-03-02 10:00:14 作者：

Normal 0 7.8 pt 0 2 false false false MicrosoftInternetExplorer4

This article discusses the security model of Microsoft SQL Server 7.0/2000 and security best practices to help you secure your data. Special thanks to my friend Divya Kalra for her valuable input and content review.

Security is a major concern for the modern age systems/network/database administrators. It is natural for an administrator to worry about hackers and external attacks while implementing security. But there is more to it. It is essential to first implement security within the organization, to make sure right people have access to the right data. Without these security measures in place, you might find someone destroying your valuable data, or selling your company's secrets to your competitors or someone invading the privacy of others. Primarily a security plan must identify which users in the organization can see which data and perform which activities in the database.

SQL Server security model
To be able to access data from a database, a user must pass through two stages of authentication, one at the SQL Server level and the other at the database level. These two stages are implemented using Logins names and User accounts respectively. A valid login is required to connect to SQL Server and a valid user account is required to access a database.

Login: A valid login name is required to connect to an SQL Server instance. A login could be:

A Windows NT/2000 login that has been granted access to SQL Server

An SQL Server login, that is maintained within SQL Server

These login names are maintained within the master database. So, it is essential to backup the master database after adding new logins to SQL Server.

User: A valid user account within a database is required to access that database. User accounts are specific to a database. All permissions and ownership of objects in the database are controlled by the user account. SQL Server logins are associated with these user accounts. A login can have associated users in different databases, but only one user per database.

During a new connection request, SQL Server verifies the login name supplied, to make sure, that login is authorized to access SQL Server. This verification is called Authentication. SQL Server supports two authentication modes:

Windows authentication mode: With Windows authentication, you do not have to specify a login name and password, to connect to SQL Server. Instead, your access to SQL Server is controlled by your Windows NT/2000 account (or the group to which your account belongs to), that you used to login to the Windows operating system on the client computer/workstation. A DBA must first specify to SQL Server, all the Microsoft Windows NT/2000 accounts or groups that can connect to SQL Server

Mixed mode: Mixed mode allows users to connect using Windows authentication or SQL Server authentication. Your DBA must first create valid SQL Server login accounts and passwords. These are not related to your Microsoft Windows NT/2000 accounts. With this authentication mode, you must supply the SQL Server login and password when you connect to SQL Server. If you do not specify SQL Server login name and password, or request Windows Authentication, you will be authenticated using Windows Authentication.

Point to note is that, whatever mode you configure your SQL Server to use, you can always login using Windows authentication.

Windows authentication is the recommended security mode, as it is more secure and you don't have to send login names and passwords over the network. You should avoid mixed mode, unless you have a non-Windows NT/2000 environment or when your SQL Server is installed on Windows 95/98 or for backward compatibility with your existing applications.

SQL Server's authentication mode can be changed using Enterprise Manager (Right click on the server name and click on Properties. Go to the Security tab).

Authentication mode can also be changed using SQL DMO object model.

Here is a list of helpful stored procedures for managing logins and users:

sp_addlogin	Creates a new login that allows users to connect to SQL Server using SQL Server authentication
sp_grantlogin	Allows a Windows NT/2000 user account or group to connect to SQL Server using Windows authentication
sp_droplogin	Drops an SQL Server login
sp_revokelogin	Drops a Windows NT/2000 login/group from SQL Server
sp_denylogin	Prevents a Windows NT/2000 login/group from connecting to SQL Server
sp_password	Adds or changes the password for an SQL Server login
sp_helplogins	Provides information about logins and their associated users in each database
sp_defaultdb	Changes the default database for a login
sp_grantdbaccess	Adds an associated user account in the current database for an SQL Server login or Windows NT/2000 login
sp_revokedbaccess	Drops a user account from the current database
sp_helpuser	Reports information about the Microsoft users and roles in the current database

Now let's talk about controlling access to objects within the database and managing permissions. Apart from managing permissions at the individual database user level, SQL Server 7.0/2000 implements permissions using roles. A role is nothing but a group to which individual logins/users can be added, so that the permissions can be applied to the group, instead of applying the permissions to all the individual logins/users. There are three types of roles in SQL Server 7.0/2000:

Fixed server roles

Fixed database roles

Application roles

Fixed server roles: These are server-wide roles. Logins can be added to these roles to gain the associated administrative permissions of the role. Fixed server roles cannot be altered and new server roles cannot be created. Here are the fixed server roles and their associated permissions in SQL Server 2000:

Fixed server role	Description
sysadmin	Can perform any activity in SQL Server
serveradmin	Can set server-wide configuration options, shut down the server
setupadmin	Can manage linked servers and startup procedures
securityadmin	Can manage logins and CREATE DATABASE permissions, also read error logs and change passwords
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

Here is a list of stored procedures that are helpful in managing fixed server roles:

sp_addsrvrolemember	Adds a login as a member of a fixed server role
sp_dropsrvrolemember	Removes an SQL Server login, Windows user or group from a fixed server role
sp_helpsrvrole	Returns a list of the fixed server roles
sp_helpsrvrolemember	Returns information about the members of fixed server roles
sp_srvrolepermission	Returns the permissions applied to a fixed server role

Fixed database roles: Each database has a set of fixed database roles, to which database users can be added. These fixed database roles are unique within the database. While the permissions of fixed database roles cannot be altered, new database roles can be created. Here are the fixed database roles and their associated permissions in SQL Server 2000:

Fixed database role	Description
db_owner	Has all permissions in the database
db_accessadmin	Can add or remove user IDs
db_securityadmin	Can manage all permissions, object ownerships, roles and role memberships
db_ddladmin	Can issue ALL DDL, but cannot issue GRANT, REVOKE, or DENY statements
db_backupoperator	Can issue DBCC, CHECKPOINT, and BACKUP statements
db_datareader	Can select all data from any user table in the database
db_datawriter	Can modify any data in any user table in the database
db_denydatareader	Cannot select any data from any user table in the database
db_denydatawriter	Cannot modify any data in any user table in the database

Here is a list of stored procedures that are helpful in managing fixed database roles:

sp_addrole	Creates a new database role in the current database
sp_addrolemember	Adds a user to an existing database role in the current database
sp_dbfixedrolepermission	Displays permissions for each fixed database role
sp_droprole	Removes a database role from the current database
sp_helpdbfixedrole	Returns a list of fixed database roles
sp_helprole	Returns information about the roles in the current database
sp_helprolemember	Returns information about the members of a role in the current database
sp_droprolemember	Removes users from the specified role in the current database

Application roles: Application roles are another way of implementing permissions. These are quite different from the server and database roles. After creating and assigning the required permissions to an application role, the client application needs to activate this role at run-time to get the permissions associated with that application role. Application roles simplify the job of DBAs, as they don't have to worry about managing permissions at individual user level. All they need to do is to create an application role and assign permissions to it. The application that is connecting to the database activates the application role and inherits the permissions associated with that role. Here are the characteristics of application roles:

There are no built-in application roles

Application roles contain no members

Application roles need to be activated at run-time, by the application, using a password

Application roles override standard permissions. For example, after activating the application role, the application will lose all the permissions associated with the login/user account used while connecting to SQL Server and gain the permissions associated with the application role

Application roles are database specific. After activating an application role in a database, if that application wants to run a cross-database transaction, the other database must have a guest user account enabled

Here are the stored procedures that are required to manage application roles:

sp_addapprole	Adds an application role in the current database
sp_approlepassword	Changes the password of an application role in the current database
sp_dropapprole	Drops an application role from the current database
sp_setapprole	Activates the permissions associated with an application role in the current database

Now that we discussed different kinds of roles, let's talk about granting/revoking permissions to/from database users and database roles and application roles. The following T-SQL commands are used to manage permissions at the user and role level.

GRANT: Grants the specific permission (Like SELECT, DELETE etc.) to the specified user or role in the current database

REVOKE: Removes a previously granted or denied permission from a user or role in the current database

DENY: Denies a specific permission to the specified user or role in the current database

Using the above commands, permissions can be granted/denied/revoked to users/roles on all database objects. You can manage permissions at as low as the column level.

Note: There is no way to manage permissions at the row level. That is, in a given table, you can't grant SELECT permission on a specific row to User1 and deny SELECT permission on another row to User2. This kind of security can be implemented by using views and stored procedures effectively. Click here to read about row level security implementation in SQL Server databases. Just an FYI, Oracle has a feature called "Virtual Private Databases" (VPD) that allows DBAs to configure permissions at row level.

SQL Server security best practices
Here is an ideal implementation of security in a Windows NT/2000 environment with SQL Server 7.0/2000 database server:

Configure SQL Server to use Windows authentication mode

Depending upon the data access needs of your domain users, group them into different global groups in the domain

Consolidate these global groups from all the trusted domains into the Windows NT/2000 local groups in your SQL Server computer

The Windows NT/2000 local groups are then granted access to log into the SQL Server

Add these Windows NT/2000 local groups to the required fixed server roles in SQL Server

Associate these local group logins with individual user accounts in the databases and grant them the required permissions using the database roles

Create custom database roles if required, for finer control over permissions

Here is a security checklist and some standard security practices and tips:

Restrict physical access to the SQL Server computer. Always lock the server while not in use.

Make sure, all the file and disk shares on the SQL Server computer are read-only. In case you have read-write shares, make sure only the right people have access to those shares.

Use the NTFS file system as it provides advanced security and recovery features.

Prefer Windows authentication to mixed mode. If mixed mode authentication is inevitable, for backward compatibility reasons, make sure you have complex passwords for sa and all other SQL Server logins. It is recommended to have mixed case passwords with a few numbers and/or special characters, to counter the dictionary based password guessing tools and user identity spoofing by hackers.

Rename the Windows NT/2000 Administrator account on the SQL Server computer to discourage hackers from guessing the administrator password.

In a website environment, keep your databases on a different computer than the one running the web service. In other words, keep your SQL Server off the Internet, for security reasons.

Keep yourself up-to-date with the information on latest service packs and security patches released by Microsoft. Carefully evaluate the service packs and patches before applying them on the production SQL Server. Bookmark this page for the latest in the security area from Microsoft: http://www.microsoft.com/security/

If it is appropriate for your environment, hide the SQL Server service from appearing in the server enumeration box in Query Analyzer, using the /HIDDEN:YES switch of NET CONFIG SERVER command.

Enable login auditing at the Operating System and SQL Server level. Examine the audit for login failure events and look for trends to detect any possible intrusion.

If it fits your budget, use Intrusion Detection Systems (IDS), especially on high-risk online database servers. IDS can constantly analyze the inbound network traffic, look for trends and detect Denial of Service (DoS) attacks and port scans. IDS can be configured to alert the administrators upon detecting a particular trend.

Disable guest user account of Windows. Drop guest user from production databases using sp_dropuser

Do not let your applications query and manipulate your database directly using SELECT/INSERT/UPDATE/DELETE statements. Wrap these commands within stored procedures and let your applications call these stored procedures. This helps centralize business logic within the database, at the same time hides the internal database structure from client applications.

Let your users query views instead of giving them access to the underlying base tables.

Discourage applications from executing dynamic SQL statements. To execute a dynamic SQL statement, users need explicit permissions on the underlying tables. This defeats the purpose of restricting access to base tables using stored procedures and views.

Don't let applications accept SQL commands from users and execute them against the database. This could be dangerous (known as SQL injection), as a skilled user can input commands that can destroy the data or gain unauthorized access to sensitive information.

Take advantage of the fixed server and database roles by assigning users to the appropriate roles. You could also create custom database roles that suit your needs.

Carefully choose the members of the sysadmin role, as the members of the sysadmin role can do anything in the SQL Server. Note that, by default, the Windows NT/2000 local administrators group is a part of the sysadmin fixed server role.

Constantly monitor error logs and event logs for security related alerts and errors.

SQL Server error logs can reveal a great deal of information about your server. So, secure your error logs by using NTFS permissions.

Secure your registry by restricting access to the SQL Server specific registry keys like HKEY_LOCAL_MACHINESoftwareMicrosoftMSSQLServer.

If your databases contain sensitive information, consider encrypting the sensitive pieces (like credit card numbers and Social Security Numbers (SSN)). There are undocumented encryption functions in SQL Server, but I wouldn't recommend those. If you have the right skills available in your organization, develop your own encryption/decryption modules using Crypto API or other encryption libraries.

If you are running SQL Server 7.0, you could use the encryption capabilities of the Multi-Protocol net library for encrypted data exchange between the client and SQL Server. SQL Server 2000 supports encryption over all protocols using Secure Socket Layer (SSL). See SQL Server 7.0 and 2000 Books Online (BOL) for more information on this topic. Please note that, enabling encryption is always a tradeoff between security and performance, because of the additional overhead of encryption and decryption.

Prevent unauthorized access to linked servers by deleting the linked server entries that are no longer needed. Pay special attention to the login mapping between the local and remote servers. Use logins with the bare minimum privileges for configuring linked servers.

DBAs generally tend to run SQL Server service using a domain administrator account. That is asking for trouble. A malicious SQL Server user could take advantage of these domain admin privileges. Most of the times, a local administrator account would be more than enough for SQL Server service.

DBAs also tend to drop system stored procedures like xp_cmdshell and all the OLE automation stored procedures (sp_OACreate and the likes). Instead of dropping these procedures, deny EXECUTE permission on them to specific users/roles. Dropping these procedures would break some of the SQL Server functionality.

Be prompt in dropping the SQL Server logins of employees leaving the organization. Especially, in the case of a layoff, drop the logins of those poor souls ASAP as they could do anything to your data out of frustration.

When using mixed mode authentication, consider customizing the system stored procedure sp_password, to prevent users from using simple and easy-to-guess passwords.

To setup secure data replication over Internet or Wide Area Networks (WAN), implement Virtual Private Networks (VPN) . Securing the snapshot folder is important too, as the snapshot agent exports data and object scripts from published databases to this folder in the form of text files. Only the replication agents should have access to the snapshot folder.

It is good to have a tool like Lumigent Log Explorer handy, for a closer look at the transaction log to see who is doing what in the database.

Do not save passwords in your .udf files, as the password gets stored in clear text.

If your database code is proprietary, encrypt the definition of stored procedures, triggers, views and user defined functions using the WITH ENCRYPTION clause. dbLockdown is a tool that automates the insertion of the WITH ENCRYPTION clause and handles all the archiving of encrypted database objects so that they can be restored again in a single click. Click here to find out more information about this product.

In database development environments, use a source code control system like Visual Source Safe (VSS) or Rational Clear Case. Control access to source code by creating users in VSS and giving permissions by project. Reserve the 'destroy permanently' permission for VSS administrator only. After project completion, lock your VSS database or leave your developers with just read-only access.