Re: SQL 2000 table deleted...

Dan, I totally agreee they shouldnt be using live as they have both
Development & Staging servers to work on! They say its a dynamic
system whereby they need to ammend things by the hour, which to me is
the incorrect approach, but try telling that to the MD when he demands
a new report!!

One of the responsibilities of a DBA is security. As such, you are gatekeeper of the production environment and need to make sure that only authorized changes are made. This doesn't mean you need to hinder development and deployment. If you or your delegate doesn't have the bandwidth to review and promote changes, I suggest you at least require that a process be followed. For example, require that all changes be first implemented and tested in another environment and promoted to production via script. This will at least mitigate the risk of ad-hoc production changes.

When I go into Enterprise Manager and right click the Database |
Properties I can GRANT create table access to webdev but at the moment
none of the checkboxes are ticked making me wonder how they have
access in the first place? Do you have to explicity deny rather than
allow? Would this even stop them deleting DBs. tables etc?

Only the following users can create/drop dbo-owned objects:

1) db_ddladmin database role members

2) db_owner database role members

3) the dbo user (login that owns the database)

4) sysadmin server role members

Granting CREATE TABLE to a user only allows them to create objects that they own.

I had taken dbowner off but again they shouted that they couldnt do
anything so I gave them writer access whcih also allows them to delete
databases, tables etc.

A adding user to db_datawriter will permit them to modify data but does not (by itself) permit them to create/drop objects.


--
Hope this helps.

Dan Guzman
SQL Server MVP

"gstar" <gary.brett@xxxxxxxxx> wrote in message news:1179998176.830969.265670@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi & thanx again to you all for helping.

Dan, I totally agreee they shouldnt be using live as they have both
Development & Staging servers to work on! They say its a dynamic
system whereby they need to ammend things by the hour, which to me is
the incorrect approach, but try telling that to the MD when he demands
a new report!!

When I go into Enterprise Manager and right click the Database |
Properties I can GRANT create table access to webdev but at the moment
none of the checkboxes are ticked making me wonder how they have
access in the first place? Do you have to explicity deny rather than
allow? Would this even stop them deleting DBs. tables etc?

I had taken dbowner off but again they shouted that they couldnt do
anything so I gave them writer access whcih also allows them to delete
databases, tables etc.

Sorry for being thick

G


.