July 5, 2011 at 12:18 pm
The following query gives me only the 1st-level mappings, ie. only those database roles to which the server login was explicitly mapped to on database db1.
If these database roles contain other database roles nested within them, the query does not retrieve those mappings.
USE db1;
SELECT
p.name,
p.type,
r.name
FROM
sys.database_principals p
INNER JOIN
sys.database_role_members m
ON
p.principal_id = m.member_principal_id
INNER JOIN
sys.database_principals r
ON
m.role_principal_id = r.principal_id
WHERE
p.[name] = 'login1'
For example, suppose that we have mapped login1 to dbRole1 .
Also suppose that role dbRole2 is also a member of dbRole1.
Implicitly, login1 is now mapped to both roles, dbRole1 and dbRole2. In SSMS we would see both mappings.
However, both the above query and sp_helprolemember will only return the explicit mapping of login1 to dbRole1, and the implicit mapping to dbRole2 will not be returned.
Anyone have any ideas how to get the nested mapping as well?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 18, 2011 at 8:29 am
Any input anyone?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 18, 2011 at 9:22 am
are you aiming at just user created database roles or fixed roles too?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 18, 2011 at 9:33 am
What you wrote earlier makes sense. I'm using this comparison in a production vs. D/R environment analysis of settings.
When a DBA attempts to synchronize D/R role mappings according to production by manually adding the role mappings in SSMS to match those in production, we end up getting this discrepancy. The problem is that in production the server login is mapped *directly* to just one role, and the nested role mappings are taken care of automatically. In D/R when someone attempts to manually match the prod settings, they are not necessarily aware of the nested role mappings and they attempt to add all mappings explicitly for the specific login through the SSMS GUI.
Then our comparison report - http://www.sqlservercentral.com/articles/Disaster+Recovery+(DR)/71992/ - shows the discrepancy between prod and D/R, due to the way the mappings were added in the D/R.
Not sure if I'm saying this clearly... 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 18, 2011 at 9:34 am
Perry Whittle (7/18/2011)
are you aiming at just user created database roles or fixed roles too?
Both.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 18, 2011 at 9:35 am
Marios Philippopoulos (7/5/2011)
For example, suppose that we have mapped login1 to dbRole1 .Also suppose that role dbRole2 is also a member of dbRole1.
Implicitly, login1 is now mapped to both roles, dbRole1 and dbRole2.
No it's not. If dbRole1 was a member of dbRole2 it would be mapped inherently, but not via the database user, the nesting is within the roles.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply