Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2008
    Posts
    4

    Question Unanswered: How to Cursor for changing multiple dbowners

    I inherited a SQL 2000 server containing hundreds of databases with a few score owner by a former user. I need to sp_changedbuser to something else, say, 'sa'.
    My query is:
    select o.name, o.version, o.crdate, suser_sname(o.sid) as DBOwner, o.dbid, o.status,o.category, o.status2, DatabaseProperty(o.name,N'isfulltextenabled')
    from master.dbo.sysdatabases o where suser_sname(o.sid) like '%\Pete';
    I want to perform a string like this:
    select 'use '+ o.name + ' go '+ 'sp_changedbowner '+''''+'sa'+''''+','+''''+'true'+''''+' go'
    from master.dbo.sysdatabases o where suser_sname(o.sid) like '%\Pete';
    Can you help me?
    Joe

  2. #2
    Join Date
    Aug 2008
    Posts
    4
    BTW, according to Best Practice, is there a better user account to change the DBOwner to than SA?

    Joe

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    You might be better generating osql statements where you can specify the db ie:

    osql -E -d "databasename" -Q"sp_changedbonwer 'sa'"

    you can do a simple for loop in dos

    redirect select name from sysdatabases... > databases.lst

    osql -E -SServername -dmaster -Q"select name from sysdatabases..." -o databases.lst

    for /f %D in ('type databases.lst') do call changeown.bat %D

    note: use %%D if you create a .bat or .cmd file with above command.

    Changeown.bat
    @echo off
    SET DB=%1
    echo osql -E -d"%DB%" -Q"sp_changedbonwer 'sa'" -SServername >> log.txt
    osql -E -d"%DB%" -Q"sp_changedbonwer 'sa'" -SServername >> log.txt
    Last edited by PMASchmed; 07-07-09 at 17:35.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •