RE: deny truncate?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Bonj (Bonj_at_discussions.microsoft.com)
Date: 10/13/04 

Date: Wed, 13 Oct 2004 08:33:02 -0700

OK....
We have implemented a rule that any developer that needs to create tables in
a database has to be members of the db_owner role of the database as they
need to be able to create dbo. prefixed tables, as if they can't, then one
will create a table and it will be called, say
mydb.dave.davestable
and then when, say Julie, selects, she will effectively get the results from
mydb.julie.davestable
which either won't exist or else she won't get the same results as dave...

I don't need them to be db_owner for anything other than to be create and
run any existing dbo. prefixed SPs and also any future ones aswell without
the future ones being explicitly assigned to them.
e.g. I need to create a dbo. procedure, and I need to be able to create
another dbo.procedure, without having to spend time setting users permissions
from it whenever I create a new procedure.

"Bonj" wrote:

> What if I want to give users Query Analyzer-style access to my data, but
> don't want them to delete it?
> I can do
> deny delete on dbo.mytable to public
>
> which is great... assuming they don't know how to do 'truncate table'.
>
> uh?
>
> is there any way I can prevent users from truncating a table by permissions?

Relevant Pages

  • RE: deny truncate?
    ... a database has to be members of the db_owner role of the database as they ... need to be able to create dbo. ... without having to spend time setting users permissions ...
    (microsoft.public.sqlserver.security)
  • Re: Change dbo
    ... I have a database where a user is defined as the dbo on a database. ... I tried using SQL Server Manger ... The login for this person maps to dbo. ...
    (microsoft.public.sqlserver.security)
  • Re: db_owner role in SQL 2k
    ... I do the select user_nameand get dbo ... Do you mean EM lists VDW as the owner of the database? ... I presume then that you are logged in as tfs. ... So my guess is that the login tfs is in the sysadmin role on the server, ...
    (microsoft.public.sqlserver.programming)
  • Re: Groups for dbo
    ... Thanks for the instructions on changing the database owner...I suspect ... While dbo is SUPPOSED to be a special user that's not used for routine ... It's probably safer to change the database owner in any event, ... >> I'm still fairly new to SQL Server, and I'm trying to figure something ...
    (microsoft.public.sqlserver.security)
  • Re: Change UDF Owner
    ... system_function_schema -especially in the master database. ... Most good judgment comes from experience. ... dbo is in the context of a database, and can be the schema owner for the ... Users can be put in the dbo role, ...
    (microsoft.public.sqlserver.security)