articles >> general dba >> Overview of the SQL Server Security Model ...

Overview of the SQL Server Security Model and Security Best Practices

By : Vyas Kondreddi May 05, 2002

This article discusses the security model of Microsoft SQL Server 7.0 and 2000, along with 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, and 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 the 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.


The 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 a SQL Server instance. A login can be:

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

- A 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 process 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 or workstation. A DBA must 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.

One 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,” then go to the “Security” tab). 

Authentication mode can also be changed using SQL DMO object model, allowing developers to write programs to manage SQL Server security.

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 a 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 and 2000 implements permissions using roles. A role is nothing but a group to which individual logins and users can be added, so that the permissions can be applied to a group, instead of applying the permissions to all the individual logins and users.