Being administrator is not enough!!!

I got an error while attaching a database from SSMS when I did a right click on databases and clicked attach, then Selected the .mdf File to attach then it threw a common OS error 5, which means "Access Denied"(An administrator should rarely get this error)!

This made me unhappy for a while, because I was using Windows Authentication with logged on as domainname\administrator.
A possible workaround was to check permissions on the file that was being accessed by me.
I checked permissions, then took ownership, the edited and added myself but no Luck!!

Then I checked the Service Credentials that are used to run the SQL Server Service and the SQL Server Agent Service.
They were neither using NETWORK SERVICE credentials, nor Administrator.
They were configured a long long ago by myself as a user which was part of Domain Admins group.(I thought the group membership would be enough to run everything smoothly.)

So I changed the service logon credentials by
Right Click MSSQLSERVER Service => Properties => Log on=> Enter admin/network credentials.
Right Click SQL Server Agent Service => Properties => Log on=> Enter admin/network credentials.

Then Restarted MSSQLSERVER Service, which restarted both of them.
And now my Database got attached!!!


Popular Posts