Re: Table OWNER

From: Saradhi (upadrasta_at_inooga.com)
Date: 12/24/04 

Date: Fri, 24 Dec 2004 19:03:10 +0530

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

Relevant Pages

  • Re: Timewarp
    ... One as clean as that would maybe fetch ... >Was the owner using it on the trackday? ...
    (uk.rec.motorcycles)
  • Re: Timewarp
    ... One as clean as that would maybe fetch ... > Was the owner using it on the trackday? ... Prev by Date: ...
    (uk.rec.motorcycles)
  • Re: reverse IP lookup for check all doimains on the server
    ... >this read only who is owner of IP 216.127.92.54 ... hosts). ... You could write a script to fetch the web page and scrape the ...
    (comp.os.linux.misc)
  • Re: Timewarp
    ... One as clean as that would maybe fetch ... Was the owner using it on the trackday? ...
    (uk.rec.motorcycles)
  • 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)