question

MGS avatar image
MGS asked

Server role of public trumps db_owner and prevents SSMS from displaying database list?

Using SQL Server Management Studio for SQL Server 2008, I can successfully connect to the server (using SQL Server Authentication) but I cannot see the database list. When I click on the databases folder I get the error message: "The server principal "my_user_login" is not able to access the database "My_DB" under the security context. "My_user_login" is only a member of the public server role? Is this why Mangement Studio won't display contents of the databases folder? The documentation I've seen says that unless a user is specifically denied view access (s)he should be able to view all database definitions.

The user is a member of the db_owner database role for the database I'm trying to work on. Shouldn't this trump the server role of public and let me at least view the database in the SSMS Object Explorer?

sql-server-2008securityssmspermissions
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
Kev Riley avatar image
Kev Riley answered

How was the user created? Is this a restore of a db from another server? If so you may have an orphaned user, where the 'my_user_login' on the server isn't correctly associated to the 'my_user_login' on the db. This link on MSDN will guide you.

Edit: update

Sounds like you may not have the VIEW ANY DATABASE permission set

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered

While it doesn't fix your problem it may help answer your question.

The database is not the only thing that would end up under Object Explorer. You also retrieve status of the service. I suspect that you need Server Operator privileges for that functionality. Since your user only has Public he is denied the ability to populate the databases folder.

Looks like they get to do command-line management :)

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

MGS avatar image
MGS answered

Kev had the answer. GRANT VIEW ANY DATABASE TO public did it. Thanks!

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Melvyn Harbour 1 avatar image Melvyn Harbour 1 ♦♦ ·
Make sure you mark it as the answer by clicking on the tick next to it.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.