Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Suspect SQL Server 2000 Database (Part 1 of 2)


By:   |   Last Updated: 2007-03-26   |   Comments   |   Related Tips: More > Security

Problem
In your recent tip (Suspect SQL Server 2000 Database) you outlined a suspect database scenario.  I took a look at the script and tried to run it in my environment.  Unfortunately, this did not resolve my issue.  Are you aware of other causes of SQL Server 2000 suspect databases?  We have been working through a SOX audit and have been changing permissions at the file system level for our SQL Server.  Could this type of change cause a suspect database?  The timing of the permission change and the suspect databases correlate, but I am not sure if this is the true cause and affect. 

Solution
Yes - If you are changing permissions at a file system level this can cause a suspect database in SQL Server.  If SQL Server does not have rights to the file system where the databases are located, this may be causing the suspect database issue.  The error message you may be receiving in your SQL Server error log may be similar to one of the following depending on the situation:

Access is denied to file C:\Program Files\Microsoft SQL Server\MSSQL\data\YourDatabaseName.MDF.
OR
udopen: Operating system error 2(The system cannot find the file specified.) during the creation/opening of physical device C:\Program Files\Microsoft SQL Server\MSSQL\data\YourDatabaseName.MDF.
OR
FCB::Open failed: Could not open device C:\Program Files\Microsoft SQL Server\MSSQL\data\YourDatabaseName.MDF for virtual device number (VDN) 1.

Based on my research, this error message can be caused by a variety of reasons, some of which may include:

  • Removing the BUILTIN\Administrators group from the SQL Server System Administrator server role and the SQL Server service account does not have explicit rights to the Program Files directory and/or the directories where the databases exist 
  • Dropping the BUILTIN\Administrators group from SQL Server and the SQL Server service account does not have rights to the Program Files directory and/or the directories where the databases exist
  • Changing the SQL Server service account to an account that is not a Windows local administrator or does not have rights to the Program Files directory and/or the directories where the databases exist
  • Removing the SQL Server service account from having rights to the Program Files directory and/or the directories where the databases exist
  • Remove the Everyone group in Windows or the Windows group where the SQL Server service account was getting rights to the Program Files directory and/or the directories where the databases exist and the SQL Server service account does not have rights to those directories directly or via another group

Keep in mind that in order for some of these issues to take affect you need to reboot your SQL Server which is when you will notice the suspect database issues.

If you are aware of other causes for suspect databases or other error messages, kindly share your knowledge with the MSSQLTips.com community by sending an email to [email protected] and we will reference your contribution to the community as an update to this tip.

Next Steps



Last Updated: 2007-03-26


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools