June 14, 2013 at 11:19 am
I created a query, that loops through the databases, but [database name].sys.database_principals seems to only have a record for db_owner if there are no other custom roles for that user.
How do you get a true listing of roles? This user has three custom roles.. showing up in dbzlb.sys.database_principals, but when you go and right click on the user in SSMS and look under Database Role Membership it shows those three PLUS db_owner, db_securityadmin and db_ddladmin.
I even did the following.. but only got the three custom roles.
select * from zemeter_lb.sys.database_principals -- picked a user
select * from zemeter_lb.sys.database_role_members where member_principal_id like '625' -- there were three records
select * from zemeter_lb.sys.database_principals where principal_id in ('69','16400','16418')
Now the code in the stored proc that I created for my audit
==============================================
DECLARE c1 CURSOR for
SELECT name FROM master.sys.databases
OPEN c1
FETCH c1 INTO @name
WHILE @@FETCH_STATUS >= 0
BEGIN
SELECT @sql =
'INSERT INTO #tmpTable
SELECT N'''+ @name + ''', a.name, c.name, a.[SID]
FROM [' + @name + '].sys.database_principals a
JOIN [' + @name + '].sys.database_role_members b ON b.member_principal_id = a.principal_id
JOIN [' + @name + '].sys.database_principals c ON c.principal_id = b.role_principal_id
WHERE a.name != ''dbo'''
EXECUTE (@sql)
FETCH c1 INTO @name
END
CLOSE c1
DEALLOCATE c1
June 14, 2013 at 4:03 pm
this query reverse engineers the sql for the role memberships so alter as necessary, but it returns all role memberships
SELECT 'EXEC sp_addrolemember [' + dp.name + '], [' + USER_NAME(drm.member_principal_id) + '] ' AS [-- AddRolemembers]
FROM sys.database_role_members drm
INNER JOIN sys.database_principals dp ON dp.principal_id = drm.role_principal_id
where USER_NAME(drm.member_principal_id) != 'dbo'
order by drm.role_principal_id
---------------------------------------------------------------------
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply