October 1, 2008 at 5:43 am
Hi,
Can anybody tell me one query to get all database owners of one specific database.
Thanks and regards,
JMSM 😉
October 1, 2008 at 7:23 am
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
Consultant - Straight Path Solutions
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
October 1, 2008 at 9:10 am
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" 😉
October 1, 2008 at 10:43 am
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 😉
October 1, 2008 at 12:45 pm
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
October 1, 2008 at 1:37 pm
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
Consultant - Straight Path Solutions
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
October 2, 2008 at 8:07 am
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