Geek Answers Handbook

Copy SQL Server 2008 Database - File Permissions

For SQL Server 2008 Developer Edition on Vista 64 bit:

I tried to copy the database using the Vista administrator account using the attach / detach method and it failed due to a file permission error, so I gave the user that sql services work as a write and change in the directory. The copy did not work. Then I gave him full control. The copy worked.

It makes sense?

If I remove full control over the user, will this cause problems?

The strange thing is that in the existing working database with files in another directory there are no special permissions in the directory and files for the database, so why does the copy require full control?

+4
source share
2 answers

When you detach the database, the MDF / LDF files can be installed with more limited perms than you would expect, for example, exclusive to the principal who made the shutdown — perhaps the SQL Server service account or the domain account of the user who executed the squad. In the past, I had to manually add permissions to the Properties> Security tab for other users, otherwise the files act as if they were locked. See also http://www.onupdatecascade.com/2009/07/sql-server-locks-mdf-and-ldf-files-upon.html

also: http://msdn.microsoft.com/en-us/library/ms189128.aspx

(thanks GrumpyOldDBA )

+3
source

If the server and / or data you are working on does not require these restrictive permissions to be set, you can set the startup flag in SQL Server, which will override this function. I understand that Microsoft is going with this - they assume that if you disconnect the database, they don’t want anyone to leave with the file; however, I think keeping a good hacker from doing this is easier said than done, and database encryption is the best way to protect data.

In any case, there is a “Trace Flag 1802,” which is strangely named because it has nothing to do with tracing. You want to add it to your SQL startup in SQL Configuration Manager if you want to keep this behavior.

https://support.microsoft.com/en-us/kb/922804

I had the same problem and found the answer in StackExchange: https://dba.stackexchange.com/a/77683/11001

0
source

More articles:


All Articles