Limiting views on shared SQL server installations

[tomoiaga]

Is there any way that after removing "VIEW any DATABASE" from "public" users that create DB's from Plesk to view those databases in Management Studio Express ?

I know that they have to be DBO's.
Is there any way to configure plesk such that it will make a users that created a database the DBO of that database ?

Or is there other alternative ?

 

[Deltafox]

hello,

I get the same issue here. In de default plesk + microsoft sql installation and configuration, mssql users can see the list of databases on the server. I want users to only see their own database in the list.

So i removed VIEW ANY DATABASE form PUBLIC (server role) but now users cannot see any database at all. But according following information from MSDN they should, because they are DB owner:

http://msdn2.microsoft.com/en-us/library/ms189077.aspx

"To limit visibility to database metadata, deny a login the VIEW ANY DATABASE permission. After this permission is denied, a login can see only metadata for master, tempdb, and databases that the login owns."

What do i do wrong?

 

[tomoiaga]

Look again,
dbo is Plesk not the user.

 

[Deltafox]

aha yes i see!

Owner = sa

I changed owner to the user and now it works.

But this must be the default behavior in Plesk, shouldn't it?

What was your solution?

 

[tomoiaga]

I have not found a solution so far and stopped looking (maby there is one after all, I am not 100% sure about that).
This is the default behaviour of Plesk, it will set the sa as dbo and you will have to manually change the dbo for every database.
It's understandable since when a Plesk user(domain administrator) crates a DB there is no user for that DB. Still they(SWSoft) can do some adjustments on that. Figured it's just not an option for them right now so I left it alone, I don't really like SWSoft's Plesk anymore, in fact I hate it, but will get to that some other time.

 

[sergius]

Gentlemen,

Thank you for the report. We will take account it in future versions.

 

[tomoiaga]

sergius, thank you!