Re: Table OWNER

From: John Bell (jbellnewsposts_at_hotmail.com)
Date: 12/24/04

  • Next message: John Bell: "Re: Firewall ports to open" 
    Date: Fri, 24 Dec 2004 16:35:38 -0000

    Hi

    You will need to specify the owner of the object in your select statement something like:

    SELECT * FROM bdyck.PESkyTable

    If you don't specify the owner then it will look for an object owned by the current user, if that does not exist it will look for one owned by dbo.

    See the topic "Database Object Owner" in Books online for more details.

    John
      "Saradhi" <upadrasta@inooga.com> wrote in message news:eGc79zb6EHA.3236@TK2MSFTNGP15.phx.gbl...
      Hi,
      Thanks For your quick Reply.
      Now I wanted to change the owner of all my tables to 'bdyck'.
      So I used the following Cursor to do this.

      DECLARE ChangeTableOwner_cursor CURSOR FOR

      SELECT '''' + TABLE_SCHEMA + '.' + TABLE_NAME + ''', bdyck'

      FROM INFORMATION_SCHEMA.TABLES

      WHERE

      OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0

      AND TABLE_TYPE = 'BASE TABLE' /*Check the TABLE_TYPE against 'VIEW' to work with views*/

      AND TABLE_SCHEMA = 'dbo' /*To change the owner of tables owned by 'dbo' only*/

      AND TABLE_NAME LIKE 'PES%' /*To change the owner of tables starting with a particular pattern*/

      GO

      DECLARE @sql1 varchar(500)

      OPEN ChangeTableOwner_cursor

      -- Perform the first fetch.

      FETCH NEXT FROM ChangeTableOwner_cursor INTO @sql1

      -- Check @@FETCH_STATUS to see if there are any more rows to fetch.

      WHILE @@FETCH_STATUS = 0

      BEGIN

      PRINT 'SQL: ' + 'EXEC ( sp_changeobjectowner + @sql1 )' + @sql1

      -- This is executed as long as the previous fetch succeeds.

      EXEC ( 'sp_changeobjectowner' + @sql1 )

      FETCH NEXT FROM ChangeTableOwner_cursor INTO @sql1

      END

      CLOSE ChangeTableOwner_cursor

      DEALLOCATE ChangeTableOwner_cursor

      GO

      But interestingly when I go and access one of my table, it says the object doesnt exists in the DB.

      Is there anything I need to change the owner of all the tables to some user and still use the data in the tables?

      -SARADHI

        "John Bell" <jbellnewsposts@hotmail.com> wrote in message news:uu$EMsD6EHA.208@TK2MSFTNGP12.phx.gbl...
        Hi

        "Saradhi" <upadrasta@inooga.com> wrote in message
        news:OfVRfVD6EHA.3504@TK2MSFTNGP12.phx.gbl...
        1) Wts the query to know the Owner of a table?

        Select * FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME = '<Table Name>'

        2) I logged in as a user pardha whose server role is a system administrator.
        I created a table in the SQL Enterprise Manager and SQL Query Analyser, but
        the EM shows the owner of the Table as dbo. it should show as pardha as I
        created it. Is there anything wrong in my thinking?

        As you are (probably) a member of the sysadmin fixed server role you will be
        mapped onto dbo.
        http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_9qyh.asp

        John

        -SARADHI

  • Next message: John Bell: "Re: Firewall ports to open"

    Relevant Pages

    • Re: conflicting object names in sql server 2000
      ... The owner of the object will depend on the user ... On the local database server when it does the select * from ... she must specify the owner: ...
      (microsoft.public.sqlserver.server)
    • Re: Developer design Best Practices
      ... The database owner login is a special case and is not specified like a ... You can execute sp_changedbowner to specify the owner or ... > and then I move them, and the owner is dbo. ... > There is NO DBO User in our dev database!! ...
      (microsoft.public.sqlserver.server)
    • Re: Table owner is not dbo.
      ... "Kestutis Adomavicius" wrote: ... > default it is not specified, which means that it will be dbo. ... > objects on publisher is not dbo, you should specify the same owner for ...
      (microsoft.public.sqlserver.replication)
    • Re: Different ownership of objects for same user
      ... dbo.xxx, whereas when you created the other one, you did not specify the ... owner -- create object xxx. ... > I logged into enterprise manager as user 'UA' and created a table via ... I saw the owner- dbo. ...
      (microsoft.public.sqlserver.programming)
    • Re: Table owner is not dbo.
      ... For every publication article you can set destination object owner. ... which means that it will be dbo. ... objects on publisher is not dbo, you should specify the same owner for ...
      (microsoft.public.sqlserver.replication)