Can't browse databases in SSMS 2008

Question

Can't browse databases in SSMS 2008

bob.probst+sqlservercentral

SSC-Addicted

Points: 412
More actions
September 26, 2011 at 10:09 am

#248767

Walked in this morning and was able to log into our cluster just fine but when I try to expand the Databases node in the Object Explorer, I get this:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The SELECT permission was denied on the object 'syspolicy_configuration', database 'msdb', schema 'dbo'.
The SELECT permission was denied on the object 'syspolicy_system_health_state', database 'msdb', schema 'dbo'. (Microsoft SQL Server, Error: 229)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2841&EvtSrc=MSSQLServer&EvtID=229&LinkId=20476
I'm told that I'm set up as dbo on msdb.
I can query the data in the databases just fine. Just can't browse the objects (which is a huge productivity issue).
When connecting to other databases, I can browse them just fine.
This was working fine on Friday.
Any suggestions would be appreciated.

Viewing 15 posts - 1 through 15 (of 16 total)

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

I suspect someone's probably started playing around with DENY permissions (either for your user/group/role or are trying to lock down the public role incorrectly).

If your user/role definitely has permissions, then I'd look into which other groups you fall into (usually just public unless something like the EVERYONE group has been added) and whether these have any explicit DENY permissions against the schema or the objects.

I'd be pretty sure it's because something has been changed, rather than hitting any kind of bug, so go and bother your DBA's 🙂

Jared SSC Guru Points: 61793 More actions · Answer 2

Hi,

I'm not sure what you mean by "set up as dbo on msdb" as there are specific permissions associated with the user that need to be adjusted. My guess is that between Friday and today, somebody changed the role you were assigned to from the Server Roles. Have your DBA double-check your server roles and specific database mapping.

Thanks,

Jared

Jared
CE - Microsoft

bob.probst+sqlservercentral SSC-Addicted Points: 412 More actions · Answer 3

Thanks for the thoughts. I'm not dbo on msdb -- was mistaken.

Granting me explicit select rights on msdb fixed the problem. but that's not really an acceptable long term solution.

The server admin tried adding his domain account to the database as a normal user and experienced the same problem. Which is very unusual. This would certainly point to something about the public role being messed up I think.

Jared SSC Guru Points: 61793 More actions · Answer 4

What exactly is a "normal user?" Can you please provide exact permissions for the user he tried to set up? Don't forget there is a public server role and a public database role.

Thanks,

Jared

Jared
CE - Microsoft

jayw.ryan SSC Enthusiast Points: 155 More actions · Answer 5

I am the server administrator (far from a DBA)

I can recreate this issue by creating a new user. New users by default have the "public" role. They should be able to browse any Databases that they have permission to. Just trying to understand what might have changed that would be denying this access for any and all new users that are created as well as an existing user.

When I look at the 2 views in question

The SELECT permission was denied on the object 'syspolicy_configuration', database 'msdb', schema 'dbo'.

The SELECT permission was denied on the object 'syspolicy_system_health_state', database 'msdb', schema 'dbo'. (Microsoft SQL Server, Error: 229)

I see that the only permissions that are applied are the 'PolicyAdministratorRole' and it is granted select permission. Can someone verify that this is the default?

Also, can I query for Deny permissions on the database, and see if any got applied by someone else?

Jared SSC Guru Points: 61793 More actions · Answer 6

Open the Security -> Server Roles folder and double-click on public. The default is "Grant" for "Connect" on all securables. That is it (1 box checked for each securable). Check that first. Also, what is the default database for a new user? Master?

Thanks,

Jared

Jared
CE - Microsoft

jayw.ryan SSC Enthusiast Points: 155 More actions · Answer 7

Yes, that is correct. Grant is selected for all securables. Master is the default. I have tried changing the users default database to a database they have dbo over, and I get the same results.

Jared SSC Guru Points: 61793 More actions · Answer 8

Try this link to see if it helps you:

http://www.techrepublic.com/blog/datacenter/reviewing-sql-server-permissions/466

Thanks,

Jared

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 9

Also take a look at this:

http://stackoverflow.com/questions/1709150/permission-required-to-view-list-of-tables-in-management-studio

The main point of this article is that DENY will always override a GRANT...

Thanks,

Jared

Jared
CE - Microsoft

jayw.ryan SSC Enthusiast Points: 155 More actions · Answer 10

Thanks for the links, after comparing the roles on our dev and production boxes I noticed a big difference. I see that on our Dev box (currently working fine) in MSDB-Security-Roles-public If I go do securables I can see that there many entries here that are not in the the Role on Production server. More specifically it looks like all of the securables from the dbo schema are missing.

So now....how the heck did they disapear....and how do I get them added back aside from manually comparing the 2 lists and manually adding each entry back in....

Jared SSC Guru Points: 61793 More actions · Answer 11

How were your databases put onto the dev machine? Were any of the system databases restored from production?

Thanks,

Jared

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 12

Just to clarify... The securables for dbo are missing on the production server?

Thanks,

Jared

Jared
CE - Microsoft

jayw.ryan SSC Enthusiast Points: 155 More actions · Answer 13

No, its just a development environment, nothing is restored from the production server or vice versa, for all intents and purposes they are completely independant SQL 2008 servers. Yes, when I look at the securables for the public role, I see the ones that have sys as the schema, but there are none that have dbo as the schema. When I look at the tables and the views for the msdb I see all the tables and views there and they are assigned to the correct schema's as far as I can tell.

The server was updated with windows security updates (No CU's or Service Packs) but their were a couple of SQL security updates. This was over a week ago, with no problems until this morning....But I believe that this may be the reason the objects are no longer present in the public role securables....

Jared SSC Guru Points: 61793 More actions · Answer 14

Hmm... Have you tried restarting the SQL Server service? If you did not notice until yesterday, something had to happen between Friday and Monday. Someone HAD to make a change or there were other updates that you were unaware of. This is probably a good one (and I don't say this often) for Microsoft Support.

Thanks,

Jared

Jared
CE - Microsoft