Wednesday, March 7, 2012

Permission problems with sp_attach_db during installation

I successfully install SQL Express silently.

Then I try the following

-S .\INSTANCE -Q "sp_attach_db 'ATTACH_AS_NAME', 'C:\Program Files\APP_NAME\SQLData\APP_DB.MDF'"

This works...sometimes. But Many times I wll get a permissions error on the folder "C:\Program Files\APP_NAME". If I go and give "Everyone" "Full Permission" to the folder...it works fine.

My questions are:

1) What system users need access (and at what level) to this folder to do an attach? Because obviously giving everyone full access is bad.

2) Is there a better method for deploying a populated database with my application that this?

SQL service account needs modify privilege on "C:\Program Files\APP_NAME\SQLData\" path. If its running in Local system account then make sure SYSTEM or NT Authority \ System has modify privilege on the path
|||Do you know how to adjust the settings of that folder to allow that in a deployment package (Wise Install?)|||If you a trying to attach only the "mdf", use sp_attach_single_file_db and ignore your transaction log file(ldf)|||Hi,
1.) Click Start-->Run-->services.msc
2.) Check SQL Server startup account (Log On As)
3.) If its Local system then provide modify privilege for SYSTEM or NT Authority \ System for the folder "C:\Program Files\APP_NAME\SQLData\"
4.) If its some other ID provide modify privilege for the ID on "C:\Program Files\APP_NAME\SQLData\" folder.

No comments:

Post a Comment