• 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.
  

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.

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.

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.

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.

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.

Concepts

Other Resources

Help and Information