February 2, 2011 at 4:47 pm
Hi,
I have created a user and given the read access to that database. When the user is trying to see the properties of the database, he is getting the below error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Script failed for Database 'CF_AQS_Document_PROD'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+Database&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
User 'Scott' does not have permission to run DBCC showfilestats for database 'MydbD'. (Microsoft SQL Server, Error: 7983)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=7983&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
I did find the Micosoft Article about this error at the link http://support.microsoft.com/kb/980037
And they are suggesting Cumulative update 7 for SP1 of SQL Server 2008.
Question:
But we have SQL Server 2008 R2 RTM and I know there are NO Service packs for SQL Server 2008 R2 yet.
What is the fix for this error in this version? (SQL Server 2008 R2 RTM)
Thanks
February 2, 2011 at 8:46 pm
you gave him read access to the database but he is trying to run a dbcc command which required elevated access.
what permission did you give him exactly? read-only or sysadmin?
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
February 2, 2011 at 10:44 pm
DBCC showfilestats requires membership in the DB_owner fixed database role or Sysadmin fixed server role.
February 11, 2011 at 11:11 am
I have given db_datareader database role to the user.
He only required read-only access and nothing else. In this case, he can not view the database properties right?
We can not give that user db_owner database role.
Please advice
February 11, 2011 at 8:46 pm
db_datareader role will not be able to run DBCC. If you don't want user to have more than db_datareader permission, why do you want user to run DBCC? Can't expect user to do admin task if you don't want to give them the rights to do it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply