Post reply

query of databases and owning users

  • May 11, 2004 at 8:14 pm

    #359643

    All,

       Does anyone have an example query that lists all databases in an instance and the owners of the databases. Have got as far as master.dbo.sysdatabases but need to find the correlating item between users and databases.

    thanks in advance

    Mark

  • May 12, 2004 at 1:12 am

    #506077

    Mark:

    Try this:

    /* Print current owner of all databases on instance*/

    create table ##dbos (Server varchar(255),DB varchar(255),Owner varchar(255))

    go

    sp_MSforeachdb @command1='insert ##dbos select @@servername,''?'' as Db, name as Owner from master..syslogins where sid = (select sid from [?].dbo.sysusers where name=''dbo'')'

    select * from ##dbos

    drop table ##dbos

    --ENDS--

    Or, the rather simpler:

    sp_MSforeachdb @command1='insert ##dbos select @@servername,''?'' as Db, name as Owner

    from master..syslogins where sid = (select sid from [?].dbo.sysusers where name=''dbo'')'

    ,@precommand='create table ##dbos (Server varchar(255),DB varchar(255),Owner varchar(255))'

    ,@postcommand='select * from ##dbos drop table ##dbos'

    --ENDS--

    This could also be changed to run on all servers on a network in turn if you have an account that has dbo access to them all, for auditing.

    Dave.

  • May 12, 2004 at 1:15 am

    #506078

    Dave,

      thanks for that. will give it a run.

    Mark

  • May 12, 2004 at 1:25 am

    #506080

    Try executing the sp_helpdb stored procedure (without any parameter values) in the master database which should show all databases and their owners.

    Alternatively, you can use:

    select name, suser_sname(sid) from master.dbo.sysdatabases

    (Got it from the sp_helpdb procedure)

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

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