Can run sp as Administrator but not as User/dbo
From: Simn (Simn_at_discussions.microsoft.com)
Date: 07/27/05
- Next message: Mike Epprecht (SQL MVP): "RE: Need Advice - encrypt data in transit"
- Previous message: Waldy: "Is this the correct group to post problems about proxy accounts?"
- 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: Wed, 27 Jul 2005 08:34:03 -0700
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: Need Advice - encrypt data in transit"
- Previous message: Waldy: "Is this the correct group to post problems about proxy accounts?"
- 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
|
