The user does not have permission to perform this action.

Question

The user does not have permission to perform this action.

gary1

SSC-Insane

Points: 21152
More actions
January 30, 2010 at 7:49 pm

#142022

H,
We have SQL Server 2005 Developer edition 32 bit with SP3 and I logged into the box with an account having Administrator account and connected to the default instance and ran the below query to find the Index fragmentation. But I'm getting the error saying user does not have permission to perform this task
SELECT avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats (6, NULL, NULL, NULL, 'limited')
where avg_fragmentation_in_percent>30 AND page_count>1000
Msg 297, Level 16, State 12, Line 1
The user does not have permission to perform this action.
Please advice

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

What server roles and database roles does that user belong to?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

gary1 SSC-Insane Points: 21152 More actions · Answer 2

It's a server in work group and the user logged in is a member of the Administrator account. So the user will have Administrative rights to log in SQL Server?

thank you

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 3

Administrator account on the windows machine may not necessarily give him all the privileges in SQL Server. Is the administrator account part of the sysadmin role?

In SMSS when you expand the security folder in the Object Explorer, do you see this administrator account or do you see windows groups that this Administrator account part of? If it exists can you check the roles for the account/group in SMSS?

It may sound silly but I once was helping someone over the phone and every time I would ask him if the account is a sysadmin he would say yes. He was confusing windows administrator with sysadmin. So just checking.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

vstitte (1/31/2010)
Administrator account on the windows machine may not necessarily give him all the privileges in SQL Server. Is the administrator account part of the sysadmin role?
In SMSS when you expand the security folder in the Object Explorer, do you see this administrator account or do you see windows groups that this Administrator account part of? If it exists can you check the roles for the account/group in SMSS?
It may sound silly but I once was helping someone over the phone and every time I would ask him if the account is a sysadmin he would say yes. He was confusing windows administrator with sysadmin. So just checking.

gmamata,

This is the information I was requesting.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

gary1 SSC-Insane Points: 21152 More actions · Answer 5

Sorry, it's my mistake. Actually we do not have a database with database_Id =6. That's I'm getting the error.

For the rest of the databases, I'm able to get the results.

thank for your help

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

That would also be a good cause.

Thanks for posting back.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events