Can run sp as Administrator but not as User/dbo

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
========================================
========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 THISD
B
>(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(), @.myPA
RAM,
>DATEDIFF(s,@.t1,@.t2), DATEDIFF(s,@.t2,@.t3), @.rowcount_var)
> END
>XX ELSE ROLLBACK TRANSACTION
>END
>ELSE SET @.error_var = 99
>RETURN @.error_var
> ========================================
========|||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:
>
>|||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:
[vbcol=seagreen]
>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:
>|||Ta
Simon.