User receiving "The user does not have permission to perform this action."

Question

User receiving "The user does not have permission to perform this action."

Kenneth.Fisher

SSC-Insane

Points: 20396
More actions
April 27, 2012 at 1:46 pm

#260623

I have a user receiving "The user does not have permission to perform this action." when trying to expand Management in SSMS.
The server she is trying to expand Management on is a server that was upgraded from SQL 2000 to SQL 2008. It's on SP SP3 CU2. I've granted the user read access to both Master and MSDB and db_ssisoperator on MSDB.
Specifically they are trying to see some legacy DTS packages that we have on that server. I'm not even getting to the point where she sees the Legacy entry under Management. She does see "Data-tier Applications" and "Policy Management" but that is all that appears before the error.
Thanks for any help
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Here is another thread with the same issue.

They granted execute to sp_enum_dtspackages and sp_get_dtspackage in msdb.

http://www.sqlservercentral.com/Forums/Topic340016-359-1.aspx#bm519123

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

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 2

Unfortunatly its a slightly different problem. My user can't even open the Management node without getting the error "The user does not have permission to perform this action." She isn't able to see the "Legacy" node let alone open it.

Thanks

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

DBA328 Hall of Fame Points: 3952 More actions · Answer 3

Have u ever checked windows event log and SQL Server error log? Found anything related?

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

have you granted one of the db_dts.......... msdb roles to the user?

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 5

I ended up granting "View server state" and "View any definition" to the user and that fixed the problem. I'm not sure which of them did it, but I'm working now. Thanks for all the help.

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 6

I assume you overlooked the Principle of Least Privileges. Now, the user has too much privilege than she should have and can peek into the data that she shouldn’t see.

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 7

Actually in this case those are privlages we allow our developers anyway. This is just a group that hadn't ever worked on these particular SQL Servers so they didn't have those permissions to begin with.

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

ivycool2012 Valued Member Points: 58 More actions · Answer 8

Unfortunatly its a slightly different problem. My user can't even open the Management node without getting the error "The user does not have permission to perform this action." She isn't able to see the "Legacy" node let alone open it.

Rc Helicopters[/url]|

Rc Hobby Stores[/url]

Rc Helicopter reviews[/url]

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 9

ivycool2012 (5/24/2012)
Unfortunately its a slightly different problem. My user can't even open the Management node without getting the error "The user does not have permission to perform this action." She isn't able to see the "Legacy" node let alone open it.

Actually that is almost exactly what was happening to my user. I granted "View server state" and "View any definition" and they were able to get in. I have to admit I granted them both at the same time so I'm not sure which one fixed it, but if your user doesn't already have one or both of these permissions you could give it a shot.

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]