February 21, 2016 at 7:01 pm
I'm new to SQL Permissions but our DBA assigned me permissions on a test server. The permissions allow me to Delete but I can not attach a database or create a new database.
I've been assigned to migrate a database from SQL 2008 to SQL 2012 and wanted to test first.
So here's what I did. In my new test server I copied the mdf and ldf files to my user drive as a backup.
From SSMS I deleted the database, that worked. I then copied the files back to the sqldata directory and tried attaching the database. I received the SQL Error #5, Access Denied.
I asked the DBA what I did wrong and was told, I don't have permissions to create or attach a database.
But I have permissions to Delete?
I was told that is an industry standard, I could delete because I have read / write access.
Is this true?
February 22, 2016 at 1:46 am
"Access denied" is a filesystem error, not a SQL Server error.
If you detached the database, your windows user has exclusive permissions on the MDF and LDF files. Fix that first.
-- Gianluca Sartori
February 23, 2016 at 9:07 am
To add, the service account for the SQL Server service must have rights to the mdf/ldf files.
February 25, 2016 at 6:14 am
deltajade (2/21/2016)
I was told that is an industry standard, I could delete because I have read / write access.Is this true?
I have never run into a situation where having read / write access (db_datareader / db_datawriter) in a database gave anyone DELETE permissions on a database, let alone DETACH or ATTACH. Whoever told you that either left out information in that statement or doesn't know what (s)he's talking about.
If you could DELETE the database, then you should have enough control to add the database back in. But, it's possible you had db_owner permissions on that database that allowed you to delete it but won't allow you to re-add it because the database and the permissions don't exist anymore.
What really worries me is that you said you copied the .mdf and .ldf files before deleting, but you didn't say you took the database offline before you copied the files. If you copied the files while the database was online, they are now corrupted and cannot be used to reattach the database onto SQL Server. I don't remember what kind of error that causes, but it does have access issues when trying to connect corrupt database files.
Your best bet at this point is to see if there is an actual database backup file that you can restore. That will let you know if you have backup operator permissions (if it works) or if you don't (if it fails). That problem is easily solvable by getting backup operator perms on the server so you can restore the database properly. Then you can test setting the database offline (or detaching it), copying the files somewhere save (they won't be corrupted at this point), then deleting and reattaching the database from that set of files.
If you're still getting an access error at that point, then I agree with previous suggestions that something doesn't have proper access to the file location (such as the service account).
February 25, 2016 at 8:36 am
Thanks All for the information.
It has helped.
🙂
February 25, 2016 at 8:41 am
Was it indeed corrupted .mdf and .ldf files?
Regardless, I'm glad you resolved your issue.
February 25, 2016 at 9:09 pm
Thank Brandie, But the DBA did not share.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply