Post reply

Database ownership

  • June 28, 2006 at 1:04 pm

    #60827

    Good afternoon.  Does anyone have a script that will go through all the databases on a server and print out the name of the database and the owner?  I'm trying to write a script that will go through and update the ownership of all non-dbo databases.  Thanks.

    Chris

  • June 28, 2006 at 1:47 pm

    #646473

    Should be pretty easy using an undocumented procedure (xp_execresultset).  This was recently in an article on this site..

    http://www.sqlservercentral.com/columnists/jtshyman/usingxp_execresultsettoobtaindatabasesizeinformati.asp

    The database size script could easily be modified to do what you want.

  • June 28, 2006 at 2:20 pm

    #646490

    You can use following query to get the owner of database.

    select name,suser_sname(sid) from Master..sysdatabases

     

    Thanks,

    SR

     

    Thanks,
    SR

  • June 28, 2006 at 2:36 pm

    #646493

    Thanks, SR!

  • June 29, 2006 at 8:30 am

    #646715

    When possible, avoid querying systables.  The structure isn't guaranteed to stay the same in the future versions of SQL Server.

    Utilize INFORMATION_SCHEMA views when possible.  It doesn't always give you as much as the system tables, but most of the time, it is perfect for what I have needed, and is the MS recommended way to grab data from system tables.

    select  CATALOG_NAME

    from  INFORMATION_SCHEMA.SCHEMATA

    order by CATALOG_NAME

    would give a full list of databases that you can slim down with conditions.

    Cheers!

    -Chris

  • June 29, 2006 at 8:36 am

    #646717

    Thanks for the heads up, Chris.

Viewing 6 posts - 1 through 5 (of 5 total)

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