Level 13

SQL database files never have user permissions set

I have an install package that is a basic MSI.
For all of my mdf and ldf files I have permissions set for Everyone with full permissions.

I verify the settings in the direct editor. Below is an example.
amazingcharts.mdf File [%USERDOMAIN] Everyone 1880031679 0

After install I check the files for permissions and this permission is not granted.
Is there a problem with setting permissions on mdf and ldf files for SQL Server 2012 Express?

Is there something in the msi log file that I can check to verify permissions.
Note that permissions on folders does not seem to be a problem.
Normal files in the directory also seem to inherit the folder permissions correctly, MDF and LDF files do not.

Thank you. :confused:
Labels (1)
0 Kudos
2 Replies
Level 13

I suspect this is my problem:

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

Since the installation attaches the database, the permissions are lost.

Now to figure out the correct method to set database permissions. The problem we encounter with our application is that normal users from a remote client are refused access. Or another user logged into the main server will not have access. Only the user account that installed the application.
0 Kudos
Level 17

You may need to add those users in question as a member of the db_owner fixed database role on the target database once created. The following sample script adds a login user named Domain\UserName as a database user of the MyDB database along with the db_owner fixed database role assigned.


EXEC sp_addrolemember 'db_owner', 'Domain\UserName'

Hope that helps.
0 Kudos