Re: Can run sp as Administrator but not as User/dbo
From: Sue Hoegemeier (Sue_H_at_nomail.please)
Date: 07/28/05
- Previous message: Sue Hoegemeier: "Re: Is this the correct group to post problems about proxy accounts?"
- In reply to: Simn: "Can run sp as Administrator but not as User/dbo"
- Next in thread: Simn: "Re: Can run sp as Administrator but not as User/dbo"
- Reply: Simn: "Re: Can run sp as Administrator but not as User/dbo"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Wed, 27 Jul 2005 18:44:13 -0600
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
>================================================
- Previous message: Sue Hoegemeier: "Re: Is this the correct group to post problems about proxy accounts?"
- In reply to: Simn: "Can run sp as Administrator but not as User/dbo"
- Next in thread: Simn: "Re: Can run sp as Administrator but not as User/dbo"
- Reply: Simn: "Re: Can run sp as Administrator but not as User/dbo"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
