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
Performs any activity in SQL Server. The permissions of this role span all of the other fixed server roles.
Configures server-wide settings
Adds and removes linked servers, and executes some system stored procedures, such as sp_serveroption.
Manages server logins.
Manages processes running in an instance of SQL Server.
Creates and alters databases.
Executes the BULK INSERT statement.
Executes the BULK INSERT statement.
Fixed Database Role
Has all permissions in the database.
Can add or remove user IDs.
Can manage all permissions, object ownerships, roles and role memberships.
Can issue ALL DDL, but cannot issue GRANT, REVOKE, or DENY statements.
Can issue DBCC, CHECKPOINT, and BACKUP statements.
Can select all data from any user table in the database.
Can modify any data in any user table in the database.
Cannot select any data from any user table in the database.
Cannot modify any data in any user table in the database.
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:
> 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