Suspect SQL Server 2000 Database (Part 1 of 2)

By: Jeremy Kadlec   |   Comments   |   Related: > 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:

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

• This tip and the previous tip (Suspect SQL Server 2000 Database) are a great example of a symptom that can have multiple causes.  While the script from the first tip would not have resolved the issues outlined in this tip and the file permissions would not have resolved corruption, the overall symptoms and observations are the same.  As such, make sure you research and understand the situation before taking action to resolve the issue.
• Check out the following related MSSQLTips.com tips:
  • Suspect SQL Server 2000 Database
  • Service Account Privileges
  •  <-- SQL Server Security Note --> Windows Groups to Support SQL Server 2005 Applications
  • Check out all of the MSSQLTips.com Security and Compliance tips
• Special thanks to Jay from the MSSQLTips.com community for this tip recommendation.

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips