db_owner vs db_ddladmin - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

db_owner vs db_ddladmin

Author

Topic

beanster
Starting Member

4 Posts

Posted - 2006-02-02 : 09:47:15

Hi,

Sorry is i'm missing something obvious but...

We're on SQL Server 2000 and i have a load of sql and windows logins with db_owner access. I want to be able to disconnect them while i do some repairs to a few databases.

If i change their access to db_datareader, db_datawriter & db_ddladmin and then use change the databases to dbo access only this should work shouldn't it? Or is there a better way?

Many thanx

Matt

mr_mist
Grunnio

1870 Posts

Posted - 2006-02-02 : 10:53:14

Changing roles etc does not affect (IE kill off) current logins.

-------
Moo. :)

beanster
Starting Member

4 Posts

Posted - 2006-02-03 : 04:11:25

thanks mist, yep i know that.

It was more a (very poorly worded) question of if they loose any access rights other than being able to change security and run backups.

Basically i'm a bit worried about applying this change to logins that 3rd party companies have installed, but unless i do i can't use the restrict access to dbo. Which unless i'm missing something is the only way to lock everyone out while i perform repairs/moves.

mr_mist
Grunnio

1870 Posts

Posted - 2006-02-03 : 04:44:58

They would lose the ability to run stored procs that they didn't have explicit exec permission on.

-------
Moo. :)

beanster
Starting Member

4 Posts

Posted - 2006-02-03 : 05:52:47

good point, will check it out. many thanks