Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 access is denied (problems with attaching a DB)

Author  Topic 

arian
Starting Member

5 Posts

Posted - 2010-08-07 : 07:02:35
It is my first post here, so I have to say hello everyone :)

I am new to SQL and
all I have done in SQL server is a project with tree different databases that I use their entries in a C# program.

I had Win xp, SQL 2005 and Visuall studio 2005.

Some days ago I installed
Win 7,SQL 2008 and Visual studio 2008.

The problem is that
I can't have any of my databases attached to SQL.
Any time I try to attach a DB I get this error:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'G:\AI\dataBase\myWord\myWords.mdf'. (Microsoft SQL Server, Error: 5123)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476


I can create/attach/detach new DBs with no problems for one time!!
Yesterday I created a test Data Base and eveything was all right but today I cant have it attached and I get the same error.

I read many forums about this problem and I guess
it is what is refered to as the NTFS problem.
Can you please help me to solve it?

Tnx
Arian

PS the fstream option is active with full access.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-07 : 07:46:06
System Error 5 means you have not enough privileges on system disk to read the file.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-08-07 : 09:07:09
Check the NTFS permissions of the mdf and ldf files you are trying to attach. Does the SQL Server service account has read/write rights to those files?

Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images and binary data from SQL Server, Oracle, DB2, PostgreSQL, SQLite, and Firebird databases.
SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily
Go to Top of Page

arian
Starting Member

5 Posts

Posted - 2010-08-07 : 12:02:26
quote:
Originally posted by Yeoh Ray Mond

Check the NTFS permissions of the mdf and ldf files you are trying to attach. Does the SQL Server service account has read/write rights to those files?



Sorry
1- How should I check that?
2- What should I do if the SQL Server service account doesn't have read/write rights to those files

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-07 : 14:25:27
2) Give SQL Server Service account permissions to the file.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-08-07 : 21:22:26
Use Windows Explorer, check the Security tab.

Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images and binary data from SQL Server, Oracle, DB2, PostgreSQL, SQLite, and Firebird databases.
SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily
Go to Top of Page

arian
Starting Member

5 Posts

Posted - 2010-08-08 : 08:11:49
Please please please give me step by step instruction

I read many articles like these 4 to find out what to do:

http://stackoverflow.com/questions/2330439/access-is-denied-when-attaching-a-database/3426438#3426438

http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/ed4514fa-8d1d-4383-bbdf-fb06bfbad106

http://situsnya.wordpress.com/2008/08/31/caclsexe-display-or-modify-access-control-lists-acls-for-files-and-folders/

but I'm really stuck!

here is what I see in my PC
1-
in the page: local dist C,properties under the Group or user name section, I see
Authernicated user
SYSTEM
Administrators(chakaveh-PC\administrators)
User(chakaveh-PC\Users)

I know it sounds crazy but I edidted all of them to have the full control!

2-
In the connect to server window of SQL
server type is "Database Engine"
server name is CHAKAVEH-PC
and Authentication is "windows Authentication"

3-
in the object Explorer the server is "CHAKAVEH-PC(SQL SErver10.0.1600-chakaveh-PC\chakaveh)"

I ran the following comand in cmd too but It didn't help
accesschk "CHAKAVEH-PC\chakaveh" -vc *

after running accessch I got the "access is denied" error for opening: ose, ose64,osppsvc, pla, QwAVE
the others have RW message.

4-
I dont know if I should see "chakaveh-PC" in the SQL Configuration Manager page or not.
I cant see it.
when I right click on "SQL server(mssQLSERver)" to open the properties window: "built in acount" is ticked and the value is network service (should it be local service/system?).
when I choose "this account" and write "chakaveh" as the accountname, I get "invalid parameter 0x80041008" error!



Would you kindly please tell me what should I do to get rid of this NTFS problem?

Thank you
Arian
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-08 : 09:16:14
Open Windows Explorer, goto the catalog and right click and choose Properties from the context menu.
Click Security and add the SQL Server Service Account to the list and assign Modify rights. Click OK twice.
Now create your file.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

arian
Starting Member

5 Posts

Posted - 2010-08-08 : 11:19:29
Hello
Thank you for your support :)

to add the SQL Server Service:
after clicking on the "add" button, I clicked on "advanced" in the new opened window then
I clicked on "find now".
I couldn't find any name starting with "SQL server Agent" in the search result section so

I tried what you said for

1-chanakevh
2-local service
3-network service
4-and anything that has the name starting with SQL!

but after the second click I got about 20 "access is denied" error for all of them. for example
C:\Program Files,c:\windows and MSOcache

and still I can't attach any data base to SQL.

Thanks again
Arian
Go to Top of Page

arian
Starting Member

5 Posts

Posted - 2010-08-11 : 14:36:36
Hi

some body told me:
1- open User Account Control Setting page
2- put it on "Never notify"
3- Restart the PC
4- Open SQL server page and attch the DBs
5- reset the User Account Control Setting

AND Thank God that it works :D
Go to Top of Page
   

- Advertisement -