Full Authorixation to MDF and LDF files for SQLServer2005MSSQLUser$[YOUR_MACHINE_NAME]$SQL2005 user.
changli wrote:
Hi LoRez,Could you please have a new post at the community so that we can
09-Jun-08
Hi LoRez,
Could you please have a new post at the community so that we can better
track it?
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg DeleteThis @microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
Previous Posts In This Thread:
On Tuesday, April 01, 2008 3:31 PM
dmgras wrote:
SQL Server 2005 can't attach database on Windows Vista
I am running Windows Vista on a new Dell computer with both the Developer
and Expression versions of SQL Server 2005 installed and I am experiencing
the following problem:
1) Used modified downloaded "instnwnd.sql" script to create Northwind
database in directory "C:\DevNet2008\Data" for SQL Server 2005 Express
2) Detached database in Microsoft SQL Server Management Studio by right
clicking "Nothwind" and selecting "Tasks > Detach ..."
3) Immeditately tried to re-attach by right clicking databases and selecting
"Attach ..." and adding the database file in the dialog but get the following
error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
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(error not found) while
attempting to open or create the physical file
'C:\DevNet2008\Data\Northwnd.mdf'. (Microsoft SQL Server, Error: 5123)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=0...0.3054&
------------------------------
BUTTONS:
OK
------------------------------
On Tuesday, April 01, 2008 5:59 PM
Erland Sommarskog wrote:
Re: SQL Server 2005 can't attach database on Windows Vista
Daniel Grassick (dmgrass@community.nospam) writes:
Did you try to attach it to the same instance or the other instance?
NET HELPMSG 5 will tell you that this is "Access is denied".
Thus, is is either a permissions problem - or you did not detach the
database from the original instance as you thought.
--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
On Tuesday, April 01, 2008 8:30 PM
dmgras wrote:
Thanks for your response. It doesn't make sense to me but ...
Thanks for your response. It doesn't make sense to me but ...
In Management Studio I only connected to the SQL Express instance and did
the create, detach and attempted re-attach through the UI in object explorer.
On Wednesday, April 02, 2008 3:08 AM
changli wrote:
Hi, Did you install the latest service pack SP2 for your SQL Server 2005
Hi,
Did you install the latest service pack SP2 for your SQL Server 2005
Express instance?
I performed a test at my side with SQL Server 2005 Developer Edition SP2
installed on Windows Vista Enterprise SP1, however I could not reproduce
your issue. Everything worked fine.
Now I recommend that you first run "SELECT @@VERSION" in SSMS to check if
your SQL Server 2005 instance version number is equal to or over 9.0.3042.
If not, please install SQL Server 2005 SP2 first. You can download SQL
Server 2005 SP2 from the following links:
Microsoft SQL Server 2005 Express Edition Service Pack 2
http://www.microsoft.com/downloads/details.aspx?FamilyID=31711d5d-725c-4afa-
9d65-e4465cdff1e7&displaylang=en
Microsoft SQL Server 2005 Service Pack 2
http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-
8f0c-63fa18f26d3a&displaylang=en
You may also try using T-SQL statements to see if it helps. You can attach
your database with the following T-SQL statement:
EXEC sp_attach_db @dbname = N'Northwind',
@filename1 = N'C:\DevNet2008\Data\northwnd.mdf',
@filename2 = N'C:\DevNet2008\Data\northwnd.ldf' ;
Go
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg DeleteThis @microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
On Wednesday, April 02, 2008 3:27 PM
dmgras wrote:
Thanks for the suggestions but no luck.
Thanks for the suggestions but no luck. Both the installed Developer and
Express editions have version numbers 9.00.3054.00 and I installed SP1 for my
Windows Vista Ultimate OS.
It seems it must be a permissions problem. My login "Daniel" is set as an
administrator but when I try to manually copy either the ".mdf" or ".ldf"
files I must acknowledge the prompt "You'll need to provide administrator
permission to copy to this file" for the destination folder and I then get
the User Account Control dialog with the prompt "Windows needs your
permission to continue".
When I run the T-SQL statement you suggested I get the error message:
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\DevNet2008\Data\northwnd.mdf".
Operating system error 5: "5(error not found)".
It's not good news that you can't reproduce the problem but I'm still hoping
for some inspired idea as to what could be going on!
On Wednesday, April 02, 2008 6:03 PM
Erland Sommarskog wrote:
Re: SQL Server 2005 can't attach database on Windows Vista
Daniel Grassick (dmgrass@community.nospam) writes:
What is the service account for SQL Server? What rights does this
service account have in the directory you use?
--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
On Thursday, April 03, 2008 6:13 AM
changli wrote:
Hi,In addition to Erland's suggestion, I also would like to ask you some other
Hi,
In addition to Erland's suggestion, I also would like to ask you some other
questions:
1. What is the result if you turn off User Account Control?
2. Are there any anti-virus software installed on your OS? If so, what is
the result if you turn if off?
3. What is the result if you perform a test with a different database with
different data file locations?
Look forward to your response.
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg DeleteThis @microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
On Thursday, April 03, 2008 12:07 PM
dmgras wrote:
Thanks for all the suggestions.
Thanks for all the suggestions. I've replied to an earlier post to avoid
nesting too deep. Securtiy is something I don't know a lot about but the SQL
Server Express permissions appear to have a lot of problems as explained
later. I did turn off the basic security in Norton Internet Security but I
didn't turn off the User Account Control as I didn't know how to do this and
I was focused on the permissions issues. The steps I took are as follows:
1) Checked the "Local Users and Groups" in "Comptuer Management".
Only the following users are defiined:
Administrator
Daniel
Guest
IUSR_DMGMAIN2008
There are a bunch of groups including 8 for SQL Server such as:
SQLServer2005MSSQLUser$DMGMAIN2008$MSSQLSERVER
SQLServer2005MSSQLUser$DMGMAIN2008$SQLEXPRESS
2) Checked the directory permissions for the original "C:\DevNet2008\Data"
directory.
Groups or users listed under security are:
Authenticated Users
SYSTEM
Administrators (DmgMain2008\Daniel\Administrators)
Users (DmgMain2008\Daniel\Users)
The "Admistrators" and "SYSTEM" have all permissions except "Special" and
"Authenticated Users" lacked "Full control" as well.
3) Created another "Northwind" database in the default server directory.
Created "Northwind" DB in the following directory using the unmodified
"instnwnd.sql" script :
c:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA
This directory included full control permissions for
"SQLServer2005MSSQLUser$DMGMAIN2008$SQLEXPRESS" but got the same error as did
for the original "C:\DevNet2008\Data" directory. The "northwnd.mdf" file
permissions included this same setting.
4) Checked permissions for some databases.
I deleted the "Northwind" files and recreated the database as above. The
permissions under "Database Properties" had no "Users or roles" listed and
just a link to "server permissions" which I explored in the next step.
The permissions for the "model" database was the same as for "Northwind" but
the permissions for "master" included "##MS_AgentSigningCertificate##" and
"guest" under "Users or roles" but clicking on the "Effective Permissions"
button resulted in a similar "can't access" error to the one listed under the
next step but with error number 15517.
5) Right clicked on instance "DMGMAIN2008\SQLEXPRESS (SQL Server 9.0.3054 -
DmgMain2008\Daniel)" in SSMS and selected "Properties" then "Permissions" in
the "Server Properties" window.
There are a whole bunch of "logins or roles" including four with "##" at the
start of the name such as:
Tthe other logins or roles are:
BUILTIN\Administrators
BUILTIN\Users
DmgMain2008\Daniel
DMGMAIN2008\SQLServer2005MSSQLUser$DMGMAIN2008$SQLEXPRESS
NT AUTHORITY\SYSTEM
The list of permissions for all these latter roles contain 25 permissions
for the grantor "DmgMain2008\Daniel" with nothing checked and a duplicate
entry for "Connect SQL" for grantor "sa" with "Grant" checked. Clicking the
"Effective Permissions" buttons shows all 25 permissions for the following
logins or roles:
DmgMain2008\Daniel
NT AUTHORITY\SYSTEM
Clicking this button for all other logins or roles including the four with
"##" at the start of the name results in the following error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot execute as the server principal because the principal
"DMGMAIN2008\SQLServer2005MSSQLUser$DMGMAIN2008$SQLEXPRESS" does not exist,
this type of principal cannot be impersonated, or you do not have permission.
(Microsoft SQL Server, Error: 15406)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=0...0.3054&
------------------------------
BUTTONS:
OK
------------------------------
On Thursday, April 03, 2008 6:10 PM
Erland Sommarskog wrote:
Re: SQL Server 2005 can't attach database on Windows Vista
Daniel Grassick (dmgrass@community.nospam) writes:
Sometimes ignorance is a good thing! Keep in on, even if you ever learn
how to turn it off.
One thing is missing from your list: what is the service account for
SQL Express? You find this in SQL Configuration Manager, double-click
the service and look at the Log On tab (which is the first).
--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
On Friday, April 04, 2008 12:21 PM
dmgras wrote:
Thanks Erland!
Thanks Erland! I checked the "Log On" service accounts and found:
SQL Server (MSSQLSERVER) uses account ".\Daniel"
SQL Server (SQLEXPRESS) used account "NT AUTHORITY\NetworkService"
These settings work fine on my Windows XP machines but I changed the account
for SQL Express to ".\Daniel" on the Vista machine and now the DB attach
works!
I don't understand how "NT AUTHORITY\NetworkService" can work on the XP
machines since I don't see any user or group with that name nor any login or
role under server permissions in SSMS. Should I have done something
different? I assume I need to change the account for the following services
which currently use "NetworkService":
SQL Server Integration Services
SQL Server Browser
Thanks so much!
Daniel
"Erland Sommarskog" wrote:
On Friday, April 04, 2008 6:23 PM
Erland Sommarskog wrote:
Re: SQL Server 2005 can't attach database on Windows Vista
Daniel Grassick (dmgrass@community.nospam) writes:
NetworkService is something built in.
I guess it works on XP, but not on Vista because Vista is stricter
on security and permissions.
The recommendation is to run SQL Server from a domain account, but
not one that has admin rights. I usually create a separate account
to run SQL Server from. The problem if you do this now, you would
have to grant that account the Windows privileges you need, or at
least I think so. When you specify an account at setup, Setup does
that for you.
The problem with using your own account is that you may change your
password some day (well, at least you should), and in this case, you
need to change the password for the service as well, or SQL Server won't
start.
Integration Services, yes. But I think NetworkService (or is that
LocalService) is preferred for the Browser service.) The Browser is
the one that tells a connecting client that instance ABC is on port
4711.
--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
On Saturday, April 05, 2008 10:34 AM
dmgras wrote:
Thanks again!
Thanks again! I think I'll stick with my main account with admin rights for
the short term because I don't think I know enough to troubleshoot problems.
I would greatly appreciate any references that would help me learn about all
the SQL Server security issues particularly as they relate to Windows
accounts and security. It's OK if you don't know of anything offhand because
I am very thankful for the time and help you have given me already!
Daniel
On Monday, April 07, 2008 7:58 AM
changli wrote:
Hi Daniel,Windows Vista expands its use of the principle of least privilege by
Hi Daniel,
Windows Vista expands its use of the principle of least privilege by
further reducing the privileges and access to the files, registry keys, and
firewall ports it assigns to most services. That is why the NETWORK SERVICE
account could not write to the folder by default. I think that you can also
work around this issue by explicitly assigning write permission to the
NETWORK Service account on the folder. Generally since the Network Service
account is the least privilege windows built-in account, it is not
recommended for SQL Server and SQL Server Agent services. Local User or
Domain User accounts are more appropriate for these services.
You may refer to the following articles:
Service Account
http://technet.microsoft.com/en-us/library/ms143691.aspx
Setting Up Windows Service Accounts
http://technet.microsoft.com/en-us/library/ms143504.aspx
Selecting an Account for the SQL Server Agent Service
http://technet.microsoft.com/en-us/library/ms191543.aspx
Service Accounts (Clusters)
http://technet.microsoft.com/en-us/library/ms143763.aspx
Hope this helps.
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg DeleteThis @microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
On Tuesday, April 08, 2008 11:43 AM
dmgras wrote:
Thanks for your helpful reply and the great links.
Thanks for your helpful reply and the great links. I've only glanced at the
information and it looks as if I have my work cut out for me if I really want
to understand the details. Thanks again!
Daniel
"Charles Wang[MSFT]" wrote:
On Wednesday, April 09, 2008 6:21 AM
Isaiah Okorie wrote:
SQL Express 2005 fails to attach database on Vista
Hi guys I felt I should drop a line on this issue since I just finished fighting it with some seed from the replies Erland and Charles gave.
I had created an SMO app for attaching my database. That failed on running it on Vista Home Premium Edition, but works fine on XP.
Then I peeped into the Event viewer on Vista and got confirmation it was a permission issue.
I then wrote a little bat file with the following:
osql -S "(local)\sqlinstance" -E -i "\update\isql.txt" -o "\update\osql.txt"
given that "isql.txt" exists in the executing
folder.
isql.txt contains:
EXEC sp_attach_db @dbname = N'MyDatabase',
@filename1 = N'C:\Data\db.mdf',
@filename2 = N'C:\Data\db_log.ldf' ;
Go
After running this bat, I got the error:
"create database permission denied in database master"
I then tried turning off User Account Control in Vista by going to Control Panel->User Accounts->Turn User Account Control on or off.
This means that Vista would allow my account(an admininstrator) to behave like an administrator, at least as far as common file operations are concerned!
Then Vista requires that you restart the machine.
After that I could now attach my database.
I think that the moral of the story is that Vista failed to allow SQL Express 2005 to attach my database. I didn't need to install SQL Express 2005 SP2.
There may well be a security risk here but Vista should not affect SQL operation in the way it did.
On Wednesday, April 09, 2008 9:36 AM
dmgras wrote:
Thanks for your reply and that of Isaiah.
Thanks for your reply and that of Isaiah. It is nice to know I wasn't the
only one struggling with the issue and it is interesting to see other
options. My personal belief is that working with the Service Account as
suggested by Erland and Charles is the best solution. I would be great if
Microsoft could release specific recommendations perhaps in their knowledge
base to address this issue.
"Phadermchai Kraisorakul" wrote:
On Saturday, May 03, 2008 1:00 AM
ams.gavril wrote:
Hi Daniel,I've stumbled upon the same problem today.
Hi Daniel,
I've stumbled upon the same problem today.
I am glad to see you solved your problem.
I did too ... and I had to disable UAC and change all permissions on
the folder to FULL CONTROL .. even to All Users Group. And it worked.
But that's not the best solution .. isn't it ?? :)
So I figured out a better one .. Running the Management Console under
Administrator Privileges.
Right click on Management Console and choose Run as Administrator ...
and works like a charm !
Funny .. isn't it ??
Andrei
On Thursday, June 05, 2008 3:56 PM
LoRe wrote:
RE: SQL Express 2005 fails to attach database on Vista
Something interesting I observed:
Detaching a database from SQL 2005 results in security permissions for the
file being wiped out for all users except the one I used to detach the file
(in my case a windows sys admin user). This includes the local system
account! When I added back SYSTEM, I could attach no problem. Why would
detaching wipe out security permissions for the file!?
On Monday, June 09, 2008 5:48 AM
changli wrote:
Hi LoRez,Could you please have a new post at the community so that we can
Hi LoRez,
Could you please have a new post at the community so that we can better
track it?
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg DeleteThis @microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
Submitted via EggHeadCafe - Software Developer Portal of Choice
Note to Active Forum Posters
http://www.eggheadcafe.com/tutorials/aspnet/bffd1eb4-da2f-426f-a6ba-4d...9453164 >> Stay informed about: SQL Server 2005 can't attach database on Windows Vista 
FAQ
Search
Profile
Private Messages
Log in
