Re: Can run sp as Administrator but not as User/dbo
From: Simn (Simn_at_discussions.microsoft.com)
Date: 07/28/05
- Next message: Mike Epprecht (SQL MVP): "RE: Encription for MDF files"
- Previous message: Andrew J. Kelly: "Re: Encription for MDF files"
- In reply to: Sue Hoegemeier: "Re: Can run sp as Administrator but not as User/dbo"
- Next in thread: Sue Hoegemeier: "Re: Can run sp as Administrator but not as User/dbo"
- Reply: Sue Hoegemeier: "Re: Can run sp as Administrator but not as User/dbo"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Thu, 28 Jul 2005 05:29:04 -0700
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: Mike Epprecht (SQL MVP): "RE: Encription for MDF files"
- Previous message: Andrew J. Kelly: "Re: Encription for MDF files"
- In reply to: Sue Hoegemeier: "Re: Can run sp as Administrator but not as User/dbo"
- Next in thread: Sue Hoegemeier: "Re: Can run sp as Administrator but not as User/dbo"
- Reply: Sue Hoegemeier: "Re: Can run sp as Administrator but not as User/dbo"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
