September 29, 2004 at 10:39 am
Hi all,
On several servers I have many databases owned by many different people. Each user is dbo for their respective database. I would like to keep this configuration but still impose some additional restrictions.
I would like to prevent the user from changing any of their files to allow Autogrow. Can this be done by restricting access to sysAltFiles?
I would also like to prevent them from creating backups of their databases.
Any thoughts?
Sincerely,
Dan B.
September 30, 2004 at 10:13 am
I don't know if you can prevent dbo from changing the autogrow option, but I know that you can't prevent dbo from performing database backups.
dbo is a member of the db_owner fixed database role which has all database permissions. Permissions can't be denied to db_owner and dbo can't be removed from the role.
Greg
Greg
October 1, 2004 at 8:43 am
Why do you want to give them the db_owner role? If it is mainly to allow them to create tables look at the db_ddladmin role. According to BOL it allows users to Add, modify, or drop objects in the database (runs all DDLs).
Steve
October 1, 2004 at 11:54 am
For now having these users as dbo/db_owner is a business requirement. Hopefully we will be able to change this in the future, but not yet.
Why is it that db_owner can not change autogrow settings via EM, but can change them via TSQL? If they can be locked down one way, why not another? To my knowledge it is not a fn or proc, otherwise I would love to get rid of Alter Database altogether.
While db_owners certainly should have full rights within their database, functionality like performing local backups and changing autogrow settings should be considered external to the database as via these any user who is db_owner can execute a DoS against the server by filling all of the drives on the box. I would hope that this type of hole is/will be addressed in SQL 2K5.
Incidentally, I found this paper to be very interesting, if not particularly related to my issue: http://www.giac.org/practical/GSEC/KBrian_Kelley_GSEC.pdf
Sincerely,
Dan B
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply