May 22, 2013 at 7:19 am
Hello
I need to find all users that have DB_Owner or DB_SecurityAdmin on every database. I've got this script that will tell me on a per database level, but running it against each DB isn't particularly efficient!
select members.name AS UserName, RTRIM(LTRIM(roles.name)) AS RoleName
from sys.database_principals members
inner join sys.database_role_members drm
on members.principal_id = drm.member_principal_id
inner join sys.database_principals roles
on drm.role_principal_id = roles.principal_id
WHERE members.name <> 'dbo' and Roles.Name = 'db_owner' or Roles.name ='db_securityadmin'
ORDER BY members.name
I have looked at a few scripts but can't find exactly what I'm looking for.
Does anyone have anything that will do this?
Thank you
May 22, 2013 at 8:04 am
What about cases where a User-defined Database Role is a member of db_owner?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 22, 2013 at 8:05 am
Can use sp_MSForEachDB, like below, to get results from each database on an instance.
EXEC sp_MSForEachDB 'select members.name AS UserName, RTRIM(LTRIM(roles.name)) AS RoleName
from sys.database_principals members
inner join sys.database_role_members drm
on members.principal_id = drm.member_principal_id
inner join sys.database_principals roles
on drm.role_principal_id = roles.principal_id
WHERE members.name <> ''dbo'' and Roles.Name = ''db_owner'' or Roles.name =''db_securityadmin''
ORDER BY members.name'
May 22, 2013 at 2:10 pm
UconnDBA (5/22/2013)
Can use sp_MSForEachDB, like below, to get results from each database on an instance.EXEC sp_MSForEachDB 'select members.name AS UserName, RTRIM(LTRIM(roles.name)) AS RoleName
from sys.database_principals members
inner join sys.database_role_members drm
on members.principal_id = drm.member_principal_id
inner join sys.database_principals roles
on drm.role_principal_id = roles.principal_id
WHERE members.name <> ''dbo'' and Roles.Name = ''db_owner'' or Roles.name =''db_securityadmin''
ORDER BY members.name'
If you are in the habit of using sp_MSforeachdb you should read this article:
A more reliable and more flexible sp_MSforeachdb by Aaron Bertrand
The proc is undocumented and while it may not change anytime soon, it has some serious shortcomings that you should know about before choosing it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 23, 2013 at 7:37 am
I have found a script that seems to work ok against a 2008 server, but not against a 2005 server.
Unfortunately it has sp_MSForEachDB in it, something that may well be the cause of the problem.
I'll see if I can do it another way...
May 23, 2013 at 9:45 am
All sp_MSForEachDB does is implement a cursor for you dynamically and run your code against each DB. Check the article I posted. The proc provided does the same thing, except it has fixed some bugs that exist within the Microsoft version.
The alternative is to implement your own cursor declaratively and using dynamic SQL, pushing the results of your query into a temporary table so you can access results from all databases when the cursor has completed.
You also never answered my question about nested groups. Does your query to find member users of a specific group handle that?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 23, 2013 at 12:37 pm
OPC: Thank you for the link to the sp_foreachdb replacement. I've encountered problems with the MS version myself and found Aaron's approach very insightful. Thanks again.
May 24, 2013 at 4:48 am
No problem Ed. Happy to share. I am confident there are millions of calls made to the MS proc every day but it has some issues that should give pause. IIRC Maintenance Plans actually use the proc to iterate over databases for certain task which is a little concerning.
Does your query to find member users of a specific group handle that?
I'll save you the suspense, your query does not handle nested groups. I alluded to it before, but in order to achieve full resolution you can use a recursive CTE or some type of manual looping mechanism.
Here is a query that includes a recursive CTE for you from my cache of scripts that works for one database. You could loop over your list of databases and using dynamic sql insert the results of the query below into a temp table to gather the information you require:
WITH CTE_Roles(role_id, role_name, major_principal_type, member_id, member_name, minor_principal_type)
AS (
SELECT dprole.principal_id AS role_id,
dprole.name AS role_name,
dprole.type_desc AS major_principal_type,
dpmembers.principal_id AS member_id,
dpmembers.name AS member_name,
dpmembers.type_desc AS minor_principal_type
FROM sys.database_principals dprole
JOIN sys.database_role_members dprolemembers ON dprole.principal_id = dprolemembers.role_principal_id
JOIN sys.database_principals dpmembers ON dprolemembers.member_principal_id = dpmembers.principal_id
WHERE dprole.name IN ('db_owner', 'db_securityadmin')
UNION ALL
SELECT CR.member_id AS role_id,
CR.member_name AS role_name,
CR.major_principal_type,
dpmembers.principal_id AS member_id,
dpmembers.name AS member_name,
dpmembers.type_desc AS minor_principal_type
FROM CTE_Roles CR
JOIN sys.database_role_members drm ON CR.member_id = drm.role_principal_id
JOIN sys.database_principals dpmembers ON drm.member_principal_id = dpmembers.principal_id
)
SELECT role_id,
role_name,
major_principal_type,
member_id,
member_name,
minor_principal_type
FROM CTE_Roles
WHERE member_name != 'dbo';
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 24, 2013 at 5:32 am
opc.three (5/24/2013)
No problem Ed. Happy to share. I am confident there are millions of calls made to the MS proc every day but it has some issues that should give pause. IIRC Maintenance Plans actually use the proc to iterate over databases for certain task which is a little concerning.Does your query to find member users of a specific group handle that?
I'll save you the suspense, your query does not handle nested groups. I alluded to it before, but in order to achieve full resolution you can use a recursive CTE or some type of manual looping mechanism.
Here is a query that includes a recursive CTE for you from my cache of scripts that works for one database. You could loop over your list of databases and using dynamic sql insert the results of the query below into a temp table to gather the information you require:
WITH CTE_Roles(role_id, role_name, major_principal_type, member_id, member_name, minor_principal_type)AS (
SELECT dprole.principal_id AS role_id,
dprole.name AS role_name,
dprole.type_desc AS major_principal_type,
dpmembers.principal_id AS member_id,
dpmembers.name AS member_name,
dpmembers.type_desc AS minor_principal_type
FROM sys.database_principals dprole
JOIN sys.database_role_members dprolemembers ON dprole.principal_id = dprolemembers.role_principal_id
JOIN sys.database_principals dpmembers ON dprolemembers.member_principal_id = dpmembers.principal_id
WHERE dprole.name IN ('db_owner', 'db_securityadmin')
UNION ALL
SELECT CR.member_id AS role_id,
CR.member_name AS role_name,
CR.major_principal_type,
dpmembers.principal_id AS member_id,
dpmembers.name AS member_name,
dpmembers.type_desc AS minor_principal_type
FROM CTE_Roles CR
JOIN sys.database_role_members drm ON CR.member_id = drm.role_principal_id
JOIN sys.database_principals dpmembers ON drm.member_principal_id = dpmembers.principal_id
)
SELECT role_id,
role_name,
major_principal_type,
member_id,
member_name,
minor_principal_type
FROM CTE_Roles
WHERE member_name != 'dbo';
@opc.three
I found the script and as I'm a scripting newbie wasn't really sure. Thanks for your script I'll see if I can work out what it is doing - been a long time since I've done any programing!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply