Re: Tables created by user

From: Johnny (jballadares@integram.com)
Date: 10/23/02 

From: "Johnny" <jballadares@integram.com>
Date: Wed, 23 Oct 2002 14:09:35 -0400

Thanks for you Reply and also for made this issue clear for me.
Johnny...

"Russell Fields" <rlfields@sprynet.com> wrote in message
news:e4J5S1reCHA.1732@tkmsftngp09...
> Johnny,
>
> Perhaps your heart is settled by this time, so you don't want anything
else.
>
> However, what TroyK was pointing out was that your stored procedure CAN
see
> a person's private table... IF the stored procedure uses the qualified
table
> name of ownername.tablename instead of just tablename.
>
> There is no option to get around this.
>
> Russell Fields
> "Johnny" <jballadares@integram.com> wrote in message
> news:u6w$r7geCHA.1872@tkmsftngp11...
> > I just wonder if exist another way to do it.
> > Tks for you reply TroyK
> > Johnny...
> >
> >
> > "TroyK" <cs_troyk@junodot.com> wrote in message
> > news:ab2a01c27a08$d1750770$3bef2ecf@TKMSFTNGXA10...
> > > Johnny;
> > >
> > > Here's a script that demonstrates a couple of options for
> > > you. You can either always reference the object with the
> > > owner name, or you can take ownership of the object using
> > > the sp_changeobjectowner system stored procedure before
> > > creating your stored procs to work with it:
> > >
> > > --------- BEGIN SCRIPT ---------
> > > CREATE TABLE someowner.Test1(
> > > i int
> > > )
> > > GO
> > >
> > > INSERT INTO someowner.Test1 VALUES(1)
> > > GO
> > >
> > > CREATE PROC dbo.sprTest1
> > > @i int
> > > AS
> > > SET NOCOUNT ON
> > > SELECT i
> > > --Reference table using Owner.ObjectName...
> > > FROM someowner.Test1
> > > WHERE i = @i
> > > GO
> > >
> > > --This sproc call works OK...
> > > EXEC dbo.sprTest1 1
> > > GO
> > >
> > > --take ownership of object for dbo...
> > > EXEC sp_changeobjectowner 'someowner.Test1', 'dbo'
> > > GO
> > >
> > > --Stored proc is now trying to reference
> > > --using the old owner... <<ERROR HERE>>
> > > EXEC dbo.sprTest1 1
> > > GO
> > >
> > > --Change the proc
> > > ALTER PROC dbo.sprTest1
> > > @i int
> > > AS
> > > SET NOCOUNT ON
> > > SELECT i
> > > --Reference table using new Owner.ObjectName...
> > > FROM dbo.Test1
> > > WHERE i = @i
> > > GO
> > >
> > > --"Corrected" Stored proc will now work...
> > > EXEC dbo.sprTest1 1
> > > GO
> > >
> > > DROP PROC dbo.sprTest1
> > > DROP TABLE dbo.Test1
> > > ------------- END SCRIPT ------------
> > >
> > > HTH,
> > > TroyK, MCSD
> > >
> > >
> > > >-----Original Message-----
> > > >Tks for Reply.
> > > >Yes, but the user is creating the table using access
> > > interface and I want to
> > > >use my sp's in that table.
> > > >Also I don't want to give the user as admin permission.
> > > >It make sense to you Andrew?
> > > >Johnny...
> > > >
> > > >"Andrew J. Kelly" <akelly@targitinteractive.com> wrote in
> > > message
> > > >news:#chp9KgeCHA.1688@tkmsftngp09...
> > > >> When you create the table specify the owner:
> > > >>
> > > >> CREATE TABLE dbo.YourTable (......
> > > >>
> > > >> --
> > > >> Andrew J. Kelly SQL MVP
> > > >> Targitinteractive, Inc.
> > > >>
> > > >> Check out the PASS Community Summit - Seattle, the
> > > largest and only user
> > > >> event entirely dedicated to SQL Server, November 19-22.
> > > >> http://www.sqlpass.org/events/seattle/index.cfm
> > > >>
> > > >>
> > > >> "Johnny" <jballadares@integram.com> wrote in message
> > > >> news:OzyxdGgeCHA.1596@tkmsftngp10...
> > > >> > Hi ALL,
> > > >> > If one user create a table shows his or her name in
> > > the table as owner
> > > >as
> > > >> is
> > > >> > supposted to be.
> > > >> > Why store procedures can not see that table?
> > > >> > How can I make the sp to see that table?
> > > >> > Can I made the table owner as dbo on the fly?
> > > >> > Right now we are using dbo.sp_changeobjectowner to
> > > change the owner as
> > > >> dbo.
> > > >> > Tks for you help.
> > > >> > Johnny
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
> > > >.
> > > >
> >
> >
>
>

Relevant Pages

  • Re: Table owner in dynamic filtered merge replication
    ... > particular owner name can I access that Stored ... > procedure without prefixing the owner name directly ... What you need to do is fixing stored procedure sp_MSexclause one more time. ... Now if you will use script for creation of schema and publication (which ...
    (microsoft.public.sqlserver.replication)
  • RE: Truncated INSERT statements when using sp_generate_inserts by Vyas
    ... SET NOCOUNT ON ... This procedure is also useful to create a database setup, ... ALSO NOTE THAT THIS PROCEDURE IS NOT UPDATED TO WORK WITH NEW DATA TYPES INTRODUCED IN SQL SERVER 2005 / YUKON ... IF @owner IS NULL ...
    (microsoft.public.sqlserver.programming)
  • RE: Does stored procedure exist
    ... You need to specify the owner name, owner name plus the object name ... means the store proc does not exist. ... >But I would like to check if stored procedure exists, ... > On Error GoTo 0 ...
    (microsoft.public.vb.database)
  • Re: ADP: Cant use stored procedure on remote SQL server
    ... Not only I use dbo everywhere but I ... If you don't mention the owner when creating a new stored procedure, view, ... BTW in the database window, all the stored procedures are followed by ... Check also the owner of the SPInc stored procedure. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Unable to drop subscription by calling sp from ASP.NET
    ... This is from the stored procedure code: ... ** agent can drop a pull subscription successfully. ... So, if the user is also the job owner (merge agent, not 'distribution agent' ...
    (microsoft.public.sqlserver.replication)
Quantcast