User ‘public’ does not have permission to run DBCC showfilestats for database ‘ testing’

User ‘public’ does not have permission to run DBCC showfilestats for database ‘ testing’

This error message is raised when your user account does not have the db_owner role of a database meaning you are not the owner of the database; and you try to open the properties dialog box of a database in SSMS 2008.

Microsoft SQL Server, Error 7983

Microsoft SQL Server, Error 7983

User ‘<User>’ does not have permission to run DBCC showfilestats for database ‘<Database Name> ‘.

Cause:

This happens when you right click on a database in SSMS and click properties of the database; In response SQL Server 2008 runs the DBCC SHOWFILESTATS command to fetch the UsedSpace property and the AvailableSpace property. Because the user is not the db_owner, the user does not have permission to run the DBCC SHOWFILESTATS command. Therefore, the user cannot open the Properties dialog box in SSMS 2008.

Workaround:

UsedSpace and AvailableSpace properties do not appear any where on the database properties page, so you are stuck on an error which doesn’t really mean anything to you.

As a workaround you can get away by using T-sql to query database properties

for eg:- use this to see the database properties if you know what you need to look at.

SP_HELPDB <databasename>

if you are looking at highlevel information of all database just use this t-sql

SP_HELPDB

Often you will need to look at database properties like database create date; you can get this information using the below t-sql.

SELECT
name AS [Database Name],
create_date AS [DB Creation Date]
FROM sys.databases

Resolution:

The fix for this issue was first released in Cumulative Update 7 for SQL Server 2008 Service Pack 1. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:

979065 Cumulative update package 7 for SQL Server 2008 Service Pack 1

Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

970365 The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 1 was released

Microsoft SQL Server 2008 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 1 hotfix to an installation of SQL Server 2008 Service Pack 1. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

Refer to http://support.microsoft.com/kb/980037 for more details.

 

This entry was posted in SQL Server, T-SQL Scripts, Tricks, Troubleshooting and tagged , , . Bookmark the permalink.

Leave a Reply