It’s quite common that we use detach-attach method to migrated SQL server databases from Windows Server 2000 or 2003 to Windows server 2008. During this task, we might experience “Access Denied” error while trying to attach the databases to the new instance at Windows 2008. An access denied error sometimes occur when we try to bring a database online.
Cause
This is due to higher security measures implemented in Windows Server 2008/ Vista / 7. When the SQL Server service account and the DBA’s admin account does not have enough rights on the database file, this error would occur.
Resolution
This can be fixed with two simple commands.
TAKEOWN – This is to take ownership if files.
ICACLS – This helps in changing the file level permissions like CHMOD command in UNIX.
To learn more on TAKEOWN, type “takeown /?” in command prompt and “icacls /?” for ICACLS.
The fix is as below.
Navigate to the folder containing those migrated datafiles and log files.
Take ownership of the file by executing below command.
C:\>takeown /F "Drive:\Directory\*df" /A
Update permission for the respective users on the files with below command.
Technology enthusiast Sherbaz is a Microsoft certified SQL Server Subject Matter Expert. He had fun automating day to day task to achieve management goals. He has extensive hands-on experience in high availability deployments and support with several years of industry experience performing SQL Server installations, configuration, maintenance, backups, performance tuning, Transactional & P2P replication, migration, high availability solutions (Always on, Failover Clustering, Logshipping and Mirroring) and Disaster recovery.
Sherbaz also has basic hands-on experience in other DBMSs like MongoDB, Oracle 9i, 10g, Sybase and MySQL. 1 year on Mongodb, Oracle 9i & 10g. Completed M102 & M202 courses in MongoDB.
Sherbaz spends his spare time with automation in electronics, embedded system circuits and IOT in his private lab at home.
Also, he supports his customers and users at www.SplitExpense.in for issues with app, website or telegram chat bot @SplitExpenseBot https://t.me/SplitExpenseBot. @TheSherBot https://t.me/TheSherBot is another telegram chat bot, partially artificial intelligent, programmed and maintained by him on python and backend Mongodb database.
Sherbaz always showed interest learning new technologies and to think out of the box bringing innovation in work. Some of his work is documented @ www.sherbaz.com.
He is now busy learning 3D modeling software on his roadmap towards building his next hobby build, A 3D PRINTER.
Subscribe to his youtube channel
https://www.youtube.com/c/SherbazMohamed/
View all posts by Sherbaz