x

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?

more ▼

asked Nov 04, 2009 at 08:27 PM in Default

MGS gravatar image

MGS
12 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

more ▼

answered Nov 05, 2009 at 05:14 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

I checked for orphaned users per the link above but that's not the case. 'my_user_login' was newly created. 'my_user_login' can query against the database, it just can't see the database list in SQL Server Management Studio. I'm just looking for confirmation that it's because the user only has a server_role of public. I would think that being db_owner of one of the databases on that server would allow 'my_user_login' to see at least that database in the object explorer pane.
Nov 05, 2009 at 12:05 PM MGS
what do you get from running sp_helplogins 'my_user_login'? Also change to MyDb and run sp_helpuser 'my_user_login'. Might be easier to post the results as an edit to your original question.
Nov 05, 2009 at 12:32 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

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 :)

more ▼

answered Nov 06, 2009 at 09:54 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 37

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Nov 19, 2009 at 01:42 PM

MGS gravatar image

MGS
12 1 1 1

Make sure you mark it as the answer by clicking on the tick next to it.
Nov 20, 2009 at 10:56 AM Melvyn Harbour 1 ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1850
x163
x127
x89

asked: Nov 04, 2009 at 08:27 PM

Seen: 3588 times

Last Updated: Nov 04, 2009 at 08:27 PM