Securing Data and Log Files
The SQL Server sets file access permissions on the physical data and log files of each database to specific accounts. The permissions prevent the files from being tampered with should they reside in a directory that has open permissions. For example, if the permissions are not set and the operating system permissions on the database directory are set to Full Control for everyone any account that has access to that directory can delete or modify the database files even though they may not have SQL Server permissions to modify the database itself.
File access permissions are set during any of the following database operations: creating, attaching, detaching, modifying to add a new file, backing up, or restoring.
Administrative Considerations
Permissions are set only when the file system supports Win32 access control, for example the NTFS file system. The Database Engine cannot set permissions on files stored on raw partitions or the FAT and FAT32 file systems.
If the operating system administrator manually modifies the file permissions, the Database Engine will not try to enforce the original permissions.
If the SQL Server (MSSQLSERVER) service account is changed by using SQL Server Management Studio, Management Studio tries to add the account and correct permissions to all existing database files. It may fail for databases that are unusable at the time the service account is changed.
If the MSSQLSERVER service account is changed by using Microsoft Windows Services, the operating system administrator must grant the new service account Full Control permissions for all database and log files.
Creating a Database or Adding a New File
When a database is created, or modified to add a new file, the MSSQLSERVER service account and members of the local Administrators group are granted Full Control access on the data and log files. File access is removed for all other accounts.
Backing Up and Restoring a Database
Full Control permissions on the file created to restore into or backup to are granted to the MSSQLSERVER service account and members of the local Administrators group.
If the file already exists, and the MSSQLSERVER service account already has permissions on the file, the backup or restore operation continues. Otherwise, the Database Engine impersonates the Windows account of the connection that is performing the operation and tries to open the file. After the file is opened, permissions are granted to the MSSQLSERVER service account and members of the local Administrators group.
Standby files (undo files) are treated in the same manner.
Detaching and Attaching a Database
When you are detaching or attaching a database, the Database Engine tries to impersonate the Windows account of the connection performing the operation to guarantee that the account has permission to access the database and log files. For mixed security accounts that use SQL Server logins, the impersonation might fail.
.gif "Security note") Security Note |
|---|
We recommend that you do not attach databases from unknown or untrusted sources. Such databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database. |
The following table shows the permissions set on the database and log files after an attach or detach operation is completed, and whether the connecting account can be impersonated by the Database Engine.
Operation |
Connecting account can be impersonated |
Files permissions are granted to |
|---|---|---|
Detach |
Yes |
Only the account performing the operation. Additional accounts can be added by a operating system administrator if they are needed after the database is detached. |
Detach |
No |
The SQL Server (MSSQLSERVER) service account and members of the local Windows Administrators group. |
Attach |
Yes |
The SQL Server (MSSQLSERVER) service account and members of the local Windows Administrators group. |
Attach |
No |
The SQL Server (MSSQLSERVER) service account. |
Scenario
The following scenario demonstrates the permissions that are set when a database is created, and modified when the database is detached, and attached.
User1, a member of the dbcreator fixed server role, creates the database Sales that has files e:\Data\Sales.mdf and f:\Log\Sales.ldf. At the time the database is created, the MSSQLSERVER service account is SQLServiceAccount2, a local account. Full Control permissions on the database and log files are granted to SQLServiceAccount2 and members of the Windows Administrators group.
A decision is made to move the Sales database to another instance of SQL Server on the same server. Admin3, a member of the sysadmin fixed server role, detaches the database. The Database Engine sets the permissions on the Sales.mdf and Sales.ldf files so that only the Admin3 account has permissions to access the files.
Admin3 connects to the other instance of SQL Server, SalesServer. The MSSQLSERVER service account for the SalesServer instance is SQLSalesServiceAccount. Admin3, a SQL Server administrator on this instance, attaches the Sales database. Full Control permissions are granted to SQLSalesServiceAccount and members of the Windows Administrators group.