Re: Schema Problems

Thanks for the reply Arnie,

So there's no secure way to allow creation of objects but not deletion of
objects in SQL 2005? That seems kind of crazy.

John

"Arnie Rowland" wrote:

John,

Certainly, in the DDL Trigger, you can check the SYSTEM_USER() function to
obtain the domain\login of a user and take action accordingly.

Remember, if you GRANT permission to ALTER an object, the object can
effectively be deleted simply by redefining to inconsequential code. I.e., a
stored procedure could have nothing but a RETURN immediately after the 'AS'.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


"John" <John@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0E0FBBD6-921E-481D-B385-3739DE27B3F6@xxxxxxxxxxxxxxxx
This seems like it would affect all users, which we do not want. Only a
certain user. Would doing a username() check suffice to get the behavior
we
want?

Thanks

"Uri Dimant" wrote:

John
If the user is an owner of the object he/she will be able to delete them,
however , in SQL Server 2005 we have a new feature as DDL Triggers.

You can create a trigger on database level to DENY some users to
delete/drop
whatever


CREATE TRIGGER trg_capture_drop_table ON DATABASE FOR DROP_TABLE

AS

-- PRINT event information For DEBUG

PRINT 'DROP TABLE Issued'

-- Can investigate data returned by EventData() and react accordingly.

RAISERROR('You cannot drop tables in this database.', 16, 1)

ROLLBACK

GO

DROP TABLE dbo.f












"John" <John@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9B5D971B-416C-45B0-AF82-DFD548D9A04A@xxxxxxxxxxxxxxxx
SQL 2005

"Arnie Rowland" wrote:

What version of SQL Server are you using?

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


"John" <John@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E8E1DA25-757A-4C64-979A-038CBE8CCB7E@xxxxxxxxxxxxxxxx
I am trying to add a new developer to access our SQL 2005 box. I
created a
role called "developer" and added him to the role. In the
properties
for
the
role, I added several tables for select permission and the create
procedure.

I would like him to be able to select from certain tables and insert
into
others (doesn't seem to be too hard to do). However, I would like
to
allow
creation of sprocs and tables, buy DENY dropping any sprocs or
tables.
it
seems like the only option is to grant the "alter" permission at the
schema
level (schema for all tables is dbo).









.

Relevant Pages

  • Re: Modifying Data Directly through Studio Manager
    ... Enterprise manager (in SQL 2000) never had a problem ... Arnie Rowland, Ph.D. ... Most good judgment comes from experience. ... 'tool' to allow your customers to manage their data. ...
    (microsoft.public.sqlserver.tools)
  • Re: Domain or Workgroup
    ... Arnie Rowland, Ph.D. ... Most good judgment comes from experience. ... to connect to a server and not being properly configured. ... installed, since better security is now available, file storage may be ...
    (microsoft.public.sqlserver.clients)
  • Re: Domain or Workgroup
    ... comments which contradict others' suggestions or material in a ... Arnie Rowland, Ph.D. ... Most good judgment comes from experience. ... to connect to a server and not being properly configured. ...
    (microsoft.public.sqlserver.clients)
  • Re: Windows Authentication or SQL Server Authentication
    ... Arnie Rowland, Ph.D. ... Most good judgment comes from experience. ... then go for Windows Authentication. ... I have a new software package that is running on SQL 2005 Standard. ...
    (microsoft.public.sqlserver.security)
  • Re: NIC settings for SQL 2005 and windows 2003?
    ... Arnie Rowland, Ph.D. ... Most good judgment comes from experience. ... It would seem so, but this fellow Microsoft MVP in SQL, say's that isn't ... always use the ""Maximize date throughput for file sharing" setting. ...
    (microsoft.public.sqlserver.connect)