Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Security / January 2006

Tip: Looking for answers? Try searching our database.

View only databases that you own

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Morten - 27 Jan 2006 09:10 GMT
Hi!

I'm trying to deploy SQL Server 2005 in a hosted environment. I've looked at
this:

http://groups.google.dk/group/microsoft.public.sqlserver.programming/browse_thre
ad/thread/c25898c37cabd79b/10190ac4a993b51b?lnk=st&q=%22VIEW+ANY+DATABASE%22&rnu
m=1&hl=da#10190ac4a993b51b


and tried to set up my server the same way. My problem is that if I use:
DENY VIEW ANY DATABASE FOR test1 (the user's name is test1).

or

REVOKE VIEW ANY DATABASE public

the test1 user doesn't see any databases (except system ones) even though he
has the db_owner role in a database named "test1".

Has anyone made this work?

Morten
Kalen Delaney - 27 Jan 2006 15:46 GMT

Hi Morten

Just because you are in the db_owner role, does NOT mean you own the
database (as your subject line suggests). You are still just a user, but you
have a greater set of rights than most users. Information about which users
are in the db_owner role is only stored INSIDE the database, and without the
VIEW ANY DATABASE permission, SQL Server will not look inside the database
to see if you happen to be listed as one of the users in db_owner role.

If you want to see databases without VIEW ANY DATABASE permission, you have
to be the real owner, which is stored in sys.databases in the master
database. SQL Server can see that info without going to the database itself.

Signature

HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com

> Hi!
>
[quoted text clipped - 16 lines]
>
> Morten
Morten - 31 Jan 2006 10:40 GMT
Hi!

Thanks for your reply. That makes a lot of sense actually. How would I make
a user the "real owner" of the database?

Best regards

Morten

> Hi Morten
>
[quoted text clipped - 31 lines]
>>
>> Morten
Kalen Delaney - 31 Jan 2006 16:48 GMT

Look up the procedure sp_changedbowner in the Books Online.

Signature

HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com

> Hi!
>
[quoted text clipped - 40 lines]
>>>
>>> Morten
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.