Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to know all database owners of one database Expand / Collapse
Author
Message
Posted Wednesday, October 1, 2008 5:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:07 PM
Points: 186, Visits: 812
Hi,

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

Thanks and regards,
JMSM ;)
Post #578977
Posted Wednesday, October 1, 2008 7:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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'





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #579043
Posted Wednesday, October 1, 2008 9:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 6,196, Visits: 13,354
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"
Post #579144
Posted Wednesday, October 1, 2008 10:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:07 PM
Points: 186, Visits: 812
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 ;)
Post #579242
Posted Wednesday, October 1, 2008 12:45 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 4,065, Visits: 5,284
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
Post #579322
Posted Wednesday, October 1, 2008 1:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #579351
Posted Thursday, October 2, 2008 8:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:07 PM
Points: 186, Visits: 812
Hello everybody,
You've been fantastic, thanks a lot.
Thanks for the help.

Regards,
JMSM ;)



Post #579664
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse