Controlling Administrator Access Rights

[pabowen]

I have a unique problem. I have a customer that wants to share their SQL server between different government agencies. Each agency has their own 'dba' - err network admin that knows enough about sql to be very dangerous.

They want to provide each agencies 'dba' with enough access to manage his or her databases, but not those of another agencies. Is there a way to divide administrative access between databases (maintain it, add users, etc) but not affect another agencies database?

A possible consideration (if necessary, but less desirable) is a separate instance, but we also have an issue where multiple agencies have local administrative access to the server. Is it possible to be a local admin without SA permissions?

Thanks!

 

[SQLBill]

Give them DBO (db_owner) access only on their database.

The SYSADMIN role is mapped to DBO role on every database.

Both SYSADMIN and DBO role can do anything, but SYSADMIN can do it on all databases, DBO can only do it on the database they are assigned that role.

-SQLBill