I restored an SQL-Server 2012 database to a folder on my D-Drive. I can access the database and everything seems to work, but when I check the file access permissions of the mdf and ldf files, there are no permissions or owner listed.
The MSSQLSERVER service account has full access to the D-drive, so I would expect that the newly created mdf and ldf would inherit their permissions from the folder (which has all the permissions, including full access for the MSSQLSERVER account).
The user that is restoring the database is a member of sysadmin server role and is an administrator on the Windows server.
Why do the database files have no file access permissions listed?
best regards, Rob
Best Answer
You mention that the account used to restore the database is a local administrator. Is the account you're using to try and view the permissions also a local administrator on the server? If not then you will not be able to see the permissions.
The SQL Server database engine sets permissions manually on the database files, they do not inherit from their parent folders.
By default when a database is restored permissions on the new database files are set such that the local Administrators group and the SQL instances user (NT SERVICE\MSSQLSERVER for the default instance, NT SERVICE\MSSQL$InstanceName for named instances) are granted full control and the local Administrators group is set as the file owner. Additional permissions can be added manually.
This is true for other operations as well, such as when a database is detached. The only permissions left on a database file after detaching are full control to the account that was used to detach the database if the user detaching the database was connected to SQL using a Windows login. If the user was connected with a SQL login then the permissions on detached files are as above with restored database files.