SQL Server with Mr. Denny

Aug 6 2009   11:02AM GMT

What do you mean I can’t access my own database when trying to attach it?



Posted by: Denny Cherry
Tags:
Attaching Database
Database security
NTFS Permisions
SecurityFightClub
SQL Server

When you detach a database from Microsoft SQL (I’m talking SQL Server 2005 and up here) the SQL Server automatically changes the NTFS permissions on the file so that only the user who told the SQL Server to detach the file has access to them.  SQL Server does this to ensure that an unauthorized person isn’t able to access the data files.

However the downside to this security measure is that the SQL Server also doesn’t have access to the files when you try to attach the files back to the server, which will cause SQL Server to throw a permissions error when attaching the database weather using SQL Server Management Studio or T/SQL.  Because of this you’ll need to edit the NTFS permissions for all the files on the SQL Server and give the SQL Server service full control rights to the database files.

Denny

1  Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • BrentOzar
    This one caught me by surprise just a couple of weeks ago. Took me damn near an hour to figure it out.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: