Hungry DBA .com for DBAs who like food

Security

Users can connect to a SQL Server database using either Windows or SQL Server Authentication. Windows Authentication has certain benefits over SQL Server Authentication, primarily due to its integration with the Windows security system. Windows security provides more features such as secure validation and encryption of passwords, auditing, password expiration, minimum password length, and account lockout after multiple login requests.

SQL Server Authentication is provided for backward compatibility because applications written for SQL Server version 7.0 or earlier may require the use of SQL Server logins and passwords. Additionally, SQL Server Authentication is required when an instance of SQL Server is running on Windows 98 because Windows Authentication is not supported on Windows 98.

You can configure an instance of SQL Server to use Windows Authentication mode or mixed mode (Windows & SQL Server Authentication), thus you can never disable Windows Authentication.

If a database has a guest user it allows a user without an account on that database to login and assume the identity and privileges of the guest user. The guest user can be deleted or added to all databases except MASTER and TEMPDB, where it must always exist. To add a guest user to a database, issue the command:

EXECUTE SP_GRANTDBACCESS guest

Individual users whether setup via SQL Server or Windows Authentication, can have different server or database roles granted to them to control their level of access (if any) to different databases within the SQL Server instance. For example a user granted the db_denydatawriter and db_datareader database roles to the pubs database would only have read-access to that database. See the tables below for the complete list of SQL Server Fixed Server & Database Roles.

Fixed Server Role

Description

Sysadmin

Performs any activity in SQL Server. The permissions of this role span all of the other fixed server roles.

Serveradmin

Configures server-wide settings

Setupadmin

Adds and removes linked servers, and executes some system stored procedures, such as sp_serveroption.

Securityadmin

Manages server logins.

Processadmin

Manages processes running in an instance of SQL Server.

Dbcreator

Creates and alters databases.

Diskadmin

Executes the BULK INSERT statement.

Bulkadmin

Executes the BULK INSERT statement.

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. 

 

Best Practices 

In general SQL Server should run with Windows Only Authentication, unless the application specifically requires that SQL Server Authentication be also enabled.

You should be careful which users are created as members of the Administration Group for the Server as this also grants that user administration rights for the SQL Server instance.

You should manage user privileges by assigning the relevant fixed server and database roles to meet your requirements.


No production database should have a guest user. Also beware of what rights are granted via the public role.

If it is necessary to perform auditing for security purposes, it is possible to do so via the SQL Profiler. The main events to consider are as follows:

Security Audit
  > Audit Add DB User Event
  > Audit Add Login to Server Role Event
  > Audit Add Member to DB Role Event
  > Audit Add Role Event
  > Audit Add Login Event
  > Audit App Role Change Password Event
  > Audit Backup/Restore Event
  > Audit Change Audit Event
  > Audit DBCC Event
  > Audit Login
  > Audit Login Change Password Event
  > Audit Login Change Property Event
  > Audit Login Failed
  > Audit Login GDR Event
  > Audit Logout
  > Audit Object Derived Permission Event
  > Audit Object GDR Event
  > Audit Object Permission Event
  > Audit Server Starts and Stops
  > Audit Statement GDR Event
  > Audit Statement Permission Event