Re: deny truncate?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Keith Kratochvil (sqlguy.back2u_at_comcast.net)
Date: 10/13/04 

Date: Wed, 13 Oct 2004 11:07:03 -0500

>>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.<<

They do not need to be dbo to execute a dbo owned stored procedure. Just
have dbo issue
GRANT EXECUTE ON dbo.YourProcName TO DeveloperNameGoesHere
You can easily create a stored procedure that issues the appropriate grant
statements to the appropriate users on all stored procedures or on a subset
of stored procedures based on creation date or name. You decide what you
need and create the supporting stored procedure to do it.

Giving users the ability to create tables....I would not give them dbo
rights for this. I would let them play in their own "sandbox" by creating
tables under their account. When their sand castle (or table) has been
approved then dbocan create the table within the development environment (as
dbo).

I have used the same techniques in the past with much success. 

-- 
Keith
"Bonj" <Bonj@discussions.microsoft.com> wrote in message
news:F3F68F4F-15C4-44DA-809C-B297C4F1AD84@microsoft.com...
> 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?
    ... prefixed SPs and also any future ones aswell without ... They do not need to be dbo to execute a dbo owned stored procedure. ...
    (microsoft.public.sqlserver.security)
  • Re: xp_logevent and service broker queues
    ... With EXECUTE AS OWNER 'dbo', the database owner of your user database needs to be 'sa' in order for the security context to be 'sa' in master too. ... This queue receives all messages and starts the stored procedure sp_AsyncReceive which is owned by the dbo. ... Maybe somebody can give me a hint how to call 'sysadmin' functions under the dbo user or how to change permissions for calling these functions in a stored procedure which is started by a service broker queue. ...
    (microsoft.public.sqlserver.security)
  • Re: public role question
    ... If 'select user' is returning DBO, it means you are connecting AS DBO, and ... testing the permissions the new user has. ... Are you saying that the permissions to create the stored procedure are ... Dan D. ...
    (microsoft.public.sqlserver.security)
  • Re: Stored Procedure Disappearing
    ... Make sure that the "Record Source Qualifier" is set to dbo; ... Sylvain Lafontaine, ing. ... Did you use dbo. ... stored procedure up into several smaller ones, or is there a better way ...
    (microsoft.public.access.reports)
  • Re: ADP: Cant use stored procedure on remote SQL server
    ... Not only I use dbo everywhere but I ... If you don't mention the owner when creating a new stored procedure, view, ... BTW in the database window, all the stored procedures are followed by ... Check also the owner of the SPInc stored procedure. ...
    (microsoft.public.access.adp.sqlserver)