Re: Can run sp as Administrator but not as User/dbo

From: Sue Hoegemeier (Sue_H_at_nomail.please)
Date: 07/29/05

  • Next message: Sue Hoegemeier: "Re: Recurring Event Log Errors" 
    Date: Thu, 28 Jul 2005 21:29:40 -0600

    Because after the table is created, the rest of the
    procedure will by default look for the table myTable being
    owned by dbo. ddladmin and db_owner need to qualify the
    table name for it to be owned by dbo. If it isn't qualified,
    their login will own the table.
    db_ddladmin can execute DDL statements - those affecting
    creating, dropping, altering objects. It won't cover
    executing procedures, selecting/updating data.
    If you need the user to be able to execute DDL statements as
    well as select and update data, you could try db_ddladmin,
    db_datareader, db_datawriter. The data access and
    modifications would apply to all tables though. If that's
    still more than what is needed, you would probably want to
    look at creating a role that covers your needs outside of
    the db_ddladmin role.

    -Sue

    On Thu, 28 Jul 2005 05:29:04 -0700, "Simn"
    <Simn@discussions.microsoft.com> wrote:

    >Cheers Sue.. that worked, though one might think if all being done as User
    >(as long User allowed to create etc.) should be OK.. ho hum!
    >BTW I changed user from dbo to ddladmin role which seems OK.. Is this the
    >min. to create/drop, run sp's and view/edit data without being dbo? I'm
    >having trouble finding exactly what the fixed db roles can do in the 'Help'.
    >
    >"Sue Hoegemeier" wrote:
    >
    >> The table being created would have different owners if run
    >> under an account that is a member of sysadmin and another
    >> account that is a member of db_owner. In the create table
    >> statement, try qualifying the owner as dbo -
    >> CREATE TABLE dbo.myTable
    >>
    >> -Sue
    >>
    >> On Wed, 27 Jul 2005 08:34:03 -0700, "Simn"
    >> <Simn@discussions.microsoft.com> wrote:
    >>
    >> >Anyone any clues? TIA Simn.
    >> >
    >> >I can run the following sp fine in isql (and vb app) when logged in as
    >> >Administrator, but not when logged in as a User who has dbo rights in THISDB
    >> >(would rather have less..) and public in OTHERDB. Am using Windows auth and
    >> >not allowed sql login.
    >> >I get the msgs:
    >> >=======
    >> >Server: Msg 208, Level 16, State 1, Procedure sp_MyPROC, Line (marked below
    >> >with X)
    >> >Invalid object name 'myTABLE'.
    >> >Server: Msg 266, Level 16, State 1, Procedure sp_MyPROC, Line (marked below
    >> >with XX)
    >> >Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
    >> >TRANSACTION statement is missing. Previous count = 0, current count = 1.
    >> >=======
    >> >
    >> >
    >> >================================================
    >> >ALTER PROC [dbo].[sp_MyPROC] @myPARAM VARCHAR(30) AS
    >> >DECLARE @error_var int
    >> >SET @error_var = 999
    >> >
    >> >IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
    >> >object_id(N'[myTABLE]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    >> >BEGIN
    >> > DECLARE @rowcount_var int
    >> > DECLARE @t1 datetime, @t2 datetime, @t3 datetime
    >> >
    >> > BEGIN TRANSACTION
    >> >
    >> > SET @t1 = GETDATE()
    >> >
    >> > CREATE TABLE [myTABLE] (
    >> > [ONE] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
    >> >X [TWO] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
    >> > ) ON [PRIMARY]
    >> >
    >> > IF( @@error <> 0 ) SET @error_var = 1
    >> >
    >> > INSERT INTO [myTABLE]
    >> > SELECT o.[ONE], o.[TWO]
    >> > FROM OTHERDB.dbo.source as o
    >> > INNER JOIN THISDB.dbo.Links as l
    >> > ON o.ONE = l.ONE
    >> > WHERE l.[Name] = @myPARAM
    >> >
    >> > SELECT @rowcount_var = @@rowcount, @error_var = @@error
    >> > IF( @error_var > 1 ) SET @error_var = 2
    >> > IF( @rowcount_var = 0 ) SET @error_var = 3
    >> >
    >> > SET @t2 = GETDATE()
    >> >
    >> > UPDATE [myTABLE] SET
    >> > [ONE]=REPLACE([SBN],'''','`'),
    >> > [TWO]=REPLACE([BNA],'''','`')
    >> >
    >> > IF( @@error <> 0 ) SET @error_var = 4
    >> > SET @t3 = GETDATE()
    >> >
    >> > IF( @error_var = 0 )
    >> > BEGIN
    >> > COMMIT TRANSACTION
    >> > INSERT INTO [timing] (RT, param, t1, t2, rc) VALUES (GETDATE(), @myPARAM,
    >> >DATEDIFF(s,@t1,@t2), DATEDIFF(s,@t2,@t3), @rowcount_var)
    >> > END
    >> >XX ELSE ROLLBACK TRANSACTION
    >> >
    >> >END
    >> >ELSE SET @error_var = 99
    >> >
    >> >RETURN @error_var
    >> >================================================
    >>
    >>

  • Next message: Sue Hoegemeier: "Re: Recurring Event Log Errors"

    Relevant Pages

    • SQL Objects Owner
      ... I have to authorize people in the db_owner group or some ... other group (ddladmin etc..) where they should be able to ... table is owned by their ID and not by dbo. ... How do you resolve this issue when people from ...
      (microsoft.public.sqlserver.programming)
    • Re: Who owns the tables in development environment? (upsizing from Access)
      ... qualify the owner as dbo when doing Access upsizing. ...
      (microsoft.public.sqlserver.clients)
    • Re: object reference in stoed proc
      ... If you don't qualify the object sql server will first look for the object ... as such it will eventually try dbo. ... implications by not qualifying the objects with the owner. ... all object refences dbo by default anyway within a stored procedure? ...
      (microsoft.public.sqlserver.security)
    • Re: Functions
      ... always qualify your objects with the owner anyway, ... > I have compiled some common functions into dbo, ... After compiling the ... > functions to the developers. ...
      (microsoft.public.sqlserver.security)
    • Re: defaulting newly created objects to DBO
      ... backwards compatibility only. ... Members of db_owner should qualify the objects they are ... creating with dbo. ...
      (microsoft.public.sqlserver.security)