August 4, 2005 at 2:35 am
Hi,
I am trying to change the default location of new databases for my MS SQL server 2000 through the SQL server properties window. But whenever I click on the "Ok" button to apply my changes, an error message with the error code 22002 appears, saying that "RegCreateKeyEx() returned error 5,'Access is denied.'".
Anyone encountered this before?
Regards,
Alvin
August 5, 2005 at 2:10 am
Well, I didn't, but it seems you should check 2 things: whether your account has sufficient privileges on SQLS (are you System Administrator?), and whether you can access the new default location and write to it.
Also, there is one note in BOL that could possibly have influence : "Data files cannot be installed on a file system using compression." Other than that, I don't know...
August 19, 2005 at 12:10 am
Hi Vladan,
Thanks for your reply. I was quite busy, and didn't really do much on the server until recently.
Ya, I tried to make those changes using my administrator account, but still got the error.
Could I also check with you on how to change the location of new databases? I have 2 partitions on my harddisk, but my databases are all on the partition with lesser harddisk space. Is there a guide I can follow on how to change database locations?
Oh, I already have 2 databases on the smaller partition already. Will that be a problem if I try to change the location?
Regards,
Alvin
August 19, 2005 at 5:39 am
You can detach the databases on the smaller drive, move them to the larger partition and then re-attach them without any problems.
Have a look in BOL for sp_detach_db and sp_attach_db for the syntax.
With this being done on the same server the only thing you might need to do is re-populate any full text indexes you have created.
September 9, 2005 at 1:20 pm
I'm experiencing the same problem, did anyone find an answer to this?
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
May 25, 2006 at 9:43 am
i've just had the same problems on a new install - i think it relates to the priveleges of the SERVICE ACCOUNT - not your login account.
MVDBA
June 5, 2006 at 2:14 pm
Use sp_attach_db in Query Analyzer rather than using Enterprise Manager;
EXEC sp_detach_db 'myDB'
go
EXEC sp_attach_db 'myDB'
,'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
,'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'
go
August 14, 2006 at 3:06 am
Add Permissions to the following key for the service account that runs SQL Server:HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT
As a domain user account running SQL Server this may not have been applied if reinstalled / uninstalled a couple of times. I've seen this same issue - the above fixed it very nicely.
October 25, 2006 at 8:02 am
I had the same issue. I made the permission change on the reg key and it worked great. Thanks for the info.
Wizer
January 3, 2008 at 7:48 am
You da man !!! That worked ..
Thanks 😎
January 4, 2008 at 8:01 am
If any of the system databases need to be moved to another drive, the simple "detach" method will not work.
The Microsoft Knowledgebase article below is good for SQL Server 7, 2000, and 2005.
Notes are for moving user databases, sample databases, Model, MSDB, Master and tempdb.
How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server
http://support.microsoft.com/kb/224071
Note also that depending on which system database(s) you are moving, SQL Server services will need to be stopped.
... and as always I recommend a full backup of each database, before beginning a move, just in case.
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply