Post reply

Database Ownership

  • June 21, 2004 at 3:04 pm

    #180793

    I was wondering if there is a way to take ownership of a database.  I need to take the ownership away from several databse from the previous dba.

    Thanks in advance for all the help.

  • June 21, 2004 at 3:11 pm

    #511326

    sp_changedbowner

    Read all about it in Books Online.  It really shouldn't be owned by a person BTW.

    Derrick Leggett
    Mean Old DBA
    When life gives you a lemon, fire the DBA.

  • June 21, 2004 at 3:17 pm

    #511327

    --Also, here's a script for you while I'm at it. 

    DECLARE

     @int_counter INT,

     @int_max INT,

     @txt_name NVARCHAR(255),

     @txt_sql NVARCHAR(2500)

    DECLARE @databases TABLE (

     int_id INT IDENTITY(1,1) PRIMARY KEY,

     txt_name NVARCHAR(255))

    INSERT @databases(txt_name)

     SELECT name FROM master..sysdatabases WHERE name NOT IN ('master','model','tempdb')

    SELECT

     @int_counter = (SELECT MIN(int_id) FROM @databases),

     @int_max = (SELECT MAX(int_id) FROM @databases)

    WHILE @int_counter <= @int_max

    BEGIN

    SELECT @txt_name = (SELECT txt_name FROM @databases WHERE int_id = @int_counter)

    SELECT @txt_sql = '

     USE ' + @txt_name + '

     sp_changedbowner ''sa'''

    --PRINT @txt_sql

    EXEC sp_executesql @txt_sql

    SELECT @int_counter = @int_counter + 1

    END

    --You might want to consider modifying to use sp_MSforeachdb.

    Derrick Leggett
    Mean Old DBA
    When life gives you a lemon, fire the DBA.

  • June 22, 2004 at 5:43 am

    #511384

    Thanks for helping out a DBA Rookie Derrick.  That script worked out GREAT!!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply