Revoking permissions from dbo

Hi,
Is there any possible way to revoke a permission in a database from someone
who is a database owner? I don't think there is, but I'm making a last ditch
attempt to find out (this is SQL Server 2005).
The reason is that we want to use the "VIEW ALL DATABASES" permission to
stop other users viewing databases that they don't own, but the only way to
do this is to make the user who is allowed to see the database the owner. Bu
t
by doing this, they get access to do things like backup databases etc which
is a security risk (this is a shared server).
All my research points to the fact that we can't stop them from backing up
databases etc if thye are the owner, and since we can't allow that, it means
"VIEW ALL DATABASES" is useless for shared hosts (whom I would think the
permission would be especially useful for if it was implemented with care).
We really want to hide all databases from other users, but we can't do it if
it means opening large holes in our security.
If anyone has any ideas before I give up, they would be appreciated!I don't have 2005 on this laptop. I believe though that you can deny view
definition on all but the databases a particular person owns. They can stil
l
see theirs, but they won't be able to see others. Isn't that, in effect, th
e
exact thing you are trying to achieve?
--
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Paul Woodland" wrote:

> Hi,
> Is there any possible way to revoke a permission in a database from someon
e
> who is a database owner? I don't think there is, but I'm making a last dit
ch
> attempt to find out (this is SQL Server 2005).
> The reason is that we want to use the "VIEW ALL DATABASES" permission to
> stop other users viewing databases that they don't own, but the only way t
o
> do this is to make the user who is allowed to see the database the owner.
But
> by doing this, they get access to do things like backup databases etc whic
h
> is a security risk (this is a shared server).
> All my research points to the fact that we can't stop them from backing up
> databases etc if thye are the owner, and since we can't allow that, it mea
ns
> "VIEW ALL DATABASES" is useless for shared hosts (whom I would think the
> permission would be especially useful for if it was implemented with care)
.
> We really want to hide all databases from other users, but we can't do it
if
> it means opening large holes in our security.
> If anyone has any ideas before I give up, they would be appreciated!
>|||Hi,
Thank you for your reply. Although doing that hides the databases fine
(thats not the problem), it's the security holes it opens up which I'm
worried about. As an example, if we set our customers as database owners the
n
they can use "backup database" whenever they wish to backup to any location
on the servers hard disk that the user SQL Server is running as has access t
o
(which is a limited number of places because it's running as a limited user,
but there are still places - such as where we store our own automated
backups). Being a shared hosting environment rather than a corporate
environment, we can't "trust" our users not to take advantage of this (for
example, they could overwrite our own backups).
The way I can think of to solve this is to somehow revoke the "backup
database" permission (and other permissions you don't want them to have)
from the user, but it won't let you revoke permissions from dbo. Which is wh
y
I'm stuck.
Any ideas?
"MeanOldDBA" wrote:
[vbcol=seagreen]
> I don't have 2005 on this laptop. I believe though that you can deny view
> definition on all but the databases a particular person owns. They can st
ill
> see theirs, but they won't be able to see others. Isn't that, in effect,
the
> exact thing you are trying to achieve?
> --
> MeanOldDBA
> derrickleggett@.hotmail.com
> http://weblogs.sqlteam.com/derrickl
> When life gives you a lemon, fire the DBA.
>
> "Paul Woodland" wrote:
>