How to know all database owners of one database

  • Hi,

    Can anybody tell me one query to get all database owners of one specific database.

    Thanks and regards,

    JMSM 😉

  • You can run this code within the specific database:

    Select

    P.name as username,

    RP.name as role_name

    from

    sys.database_role_members R Join

    sys.database_principals RP On

    R.role_principal_id = RP.principal_id Join

    sys.database_principals P On

    R.member_principal_id = P.principal_id

    Where

    RP.name = 'db_owner'

  • JMSM (10/1/2008)


    get all database owners of one specific database.

    there is only one database owner, schemas however may be owned by more than 1 object!!

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • I think that u don't understand my question.

    I've got one user that is dbo on 3 databases, db_datareader on another 3 databases and db_datawriter on more 5 databases.

    What i need is one query that can report all databases that are allocated to one specific user and all privileges that is got on them.

    Thanks and regards,

    JMSM 😉

  • No wonder the confusion. That looks like a different question.

    Does this query work for you?:

    -- CHANGE LOGINNAME TO THE LOGIN YOU'RE INTERESTED IN

    set nocount on

    declare @name sysname,

    @SQL nvarchar(600)

    if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmpTable'))

    drop table #tmpTable

    CREATE TABLE #tmpTable (

    [DATABASENAME] sysname NOT NULL ,

    [USER_NAME] sysname NOT NULL,

    [ROLE_NAME] sysname NULL,

    [LOGIN_NAME] sysname NULL)

    declare c1 cursor for

    select name from master.dbo.sysdatabases

    open c1

    fetch c1 into @name

    while @@fetch_status >= 0

    begin

    select @SQL =

    'insert into #tmpTable

    select N'''+ @name + ''', a.name, c.name, d.name

    from ' + QuoteName(@name) + '.sys.database_principals a

    left join ' + QuoteName(@name) + '.sys.database_role_members b on b.member_principal_id = a.principal_id

    left join ' + QuoteName(@name) + '.sys.database_principals c on c.principal_id = b.role_principal_id

    left join ' + 'master.sys.server_principals d on d.sid = a.sid

    where a.name <> ''dbo'' and a.is_fixed_role = 0

    and a.name = ''LOGINNAME'''

    /* Insert row for each database */

    execute (@SQL)

    fetch c1 into @name

    end

    close c1

    deallocate c1

    select * from #tmpTable

    Greg

  • JMSM (10/1/2008)


    I think that u don't understand my question.

    I've got one user that is dbo on 3 databases, db_datareader on another 3 databases and db_datawriter on more 5 databases.

    What i need is one query that can report all databases that are allocated to one specific user and all privileges that is got on them.

    Thanks and regards,

    JMSM 😉

    Actually I did understand your question, you just didn't ask the right question. Check out the links in my signature line. What you really wanted was all database roles for a specific user.

  • Hello everybody,

    You've been fantastic, thanks a lot.

    Thanks for the help.

    Regards,

    JMSM 😉

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

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