How do I drop DBO access to a DB?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TLuebke - 13 Nov 2008 15:40 GMT

I'm running an SQL 2005 in a classroom environment. Students are allowed to
create databases and did so last night for a test. The instructor wants me to
deny them access to the DB's they created so she can grade them. I can't seem
to drop them from their DB's because they are linked as DBO.

How do I deny them access to a particular DB that they created?
I've tried sp_changedowner, revoke access and a few other scripts.

Reply to this Message

Uri Dimant - 16 Nov 2008 07:54 GMT

Hi
Because those users are members of db_owner database role or even sysdamin
on the instance?
Remove them from db_owner database role and make them to login with
separate login to the instance

> I'm running an SQL 2005 in a classroom environment. Students are allowed
> to
[quoted text clipped - 6 lines]
> How do I deny them access to a particular DB that they created?
> I've tried sp_changedowner, revoke access and a few other scripts.

Reply to this Message

Erland Sommarskog - 16 Nov 2008 19:17 GMT

> I'm running an SQL 2005 in a classroom environment. Students are allowed
> to create databases and did so last night for a test. The instructor
[quoted text clipped - 4 lines]
> How do I deny them access to a particular DB that they created?
> I've tried sp_changedowner, revoke access and a few other scripts.

Changing database owner (ALTER AUTHORIZATION is the preferred in SQL 2005)
should to the trick. Since they are no longer databaser owners, they should
not be able to access the databases - unless the guest account is enabled.

An alternative would be to detach the databases, and then attach them to
a server to which only the instructor has access.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message