| Author |
Topic |
|
stephenbaer
Yak Posting Veteran
71 Posts |
 Posted - 2008-08-19 : 16:50:50
|
| I did some research since posting a previous topic, and I discovered a few things, but I'm still not able to do what I'd like.I'm looking at several system views, which I assume contain the info I need, but I can't quite figure out how they are related.The views in question:sys.database_principalssys.database_role_membersI've figured out that there are several system SPs that will list all the roles, check role membership, etc. For instance:EXEC sp_helprolemember 'Sales'will return all members of the 'sales' database role. That's swell, but I want it the other way around.How do I make a view that lists role membership for a user?-----------------Stephen |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
stephenbaer
Yak Posting Veteran
71 Posts |
Posted - 2008-08-19 : 16:56:22
|
| AHA! Man, that was easier than I thought.Observe:---------SELECT sys.database_principals.nameFROM sys.database_principals AS logins INNER JOIN sys.database_role_members ON logins.principal_id = sys.database_role_members.member_principal_id INNER JOIN sys.database_principals ON sys.database_role_members.role_principal_id = sys.database_principals.principal_idWHERE (sys.database_principals.principal_id < 16384) AND (logins.name = N'sbaer')---------Yes, I know the < 16384 isn't really complete. I need to exclude just the reserved ids, but that was a quick test. One question though...What's with the "N" in (logins.name = N'sbaer')?-----------------Stephen |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
stephenbaer
Yak Posting Veteran
71 Posts |
Posted - 2008-08-19 : 16:59:08
|
 um, yours was slighly more elegant. Thanks Tara.-----------------Stephen |
|
|
|
stephenbaer
Yak Posting Veteran
71 Posts |
Posted - 2008-08-19 : 17:40:11
|
| Why does the above work, but not this?-----------SELECT sys.database_principals.nameFROM sys.database_principals AS logins INNER JOINsys.database_role_members ON logins.principal_id = sys.database_role_members.member_principal_id INNER JOINsys.database_principals ON sys.database_role_members.role_principal_id = sys.database_principals.principal_idWHERE (sys.database_principals.principal_id < 16384) AND (logins.name = CURRENT_USER)--------------------------Stephen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
stephenbaer
Yak Posting Veteran
71 Posts |
Posted - 2008-08-19 : 17:58:44
|
| OHHHH! For me, Current_user returns dbo! hmmm-----------------Stephen |
|
|
|
stephenbaer
Yak Posting Veteran
71 Posts |
Posted - 2008-08-19 : 18:09:33
|
quote:
Originally posted by tkizerWhy don't you just use sp_helpuser?
Because I can't use it in an IN clause, at least I don't think so...Also, the front end is odbc and SP's are troublesome, so I'm trying to make a view. What can I say, I'm a noob.Here's the full query:ALTER VIEW [dbo].[new]ASSELECT dbo.Resident.*, dbo.Facility.FacilityAbbrevFROM dbo.Facility INNER JOIN dbo.Resident ON dbo.Facility.FacilityID = dbo.Resident.FacilityIDWHERE FacilityAbbrev IN (SELECT sys.database_principals.nameFROM sys.database_principals AS logins INNER JOINsys.database_role_members ON logins.principal_id = sys.database_role_members.member_principal_id INNER JOINsys.database_principals ON sys.database_role_members.role_principal_id = sys.database_principals.principal_idWHERE (sys.database_principals.principal_id < 16384) AND (logins.name = CURRENT_USER)))-----------------Stephen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
stephenbaer
Yak Posting Veteran
71 Posts |
Posted - 2008-08-19 : 18:34:20
|
 I'm sure there is...Ok, here's the long story in the shortest form possible...We have a database, which contains the names of our clients. All the clients are minors. As such, there are very strict laws concerning need-to-know. Each client resides at a given facility, as indicated by FacilityID and FacilityAbbrev, in the query above. Only the staff members at that facility, and those in the higher echelons should see info on residents at that particulr facility. Staff of other facilities should not. Right now, the front-end goes through some convoluted AD group checking, and creates a string to use as the datasource for forms, etc. Unfortunately, this means I have to create new views for special cases, such as right now, when we are down a supervisor, and a different supervisor temporarily needs access to the other facility's info, so she can fill in. That meant I had to create a new view that shows both facilities, and alter the front end to set that view as the new data source for said person. It was a pain. I thought I might implement row-level security in the database, using roles matching the facilities. Then, when someone needs access to multiple facilities (but not ALL of them), I can just add said person to the corresponding roles, and viola! Then I'll remove them once they don't need the access.-----------------Stephen |
|
|
|
stephenbaer
Yak Posting Veteran
71 Posts |
Posted - 2008-08-20 : 00:43:43
|
Just as an FYI, the view posted works great---except for me  .If I log on as anyone else, it works! Which brings me to...How do I make current_user return my actual user name instead of dbo?I know I can use the execute as user thing, but a view won't allow that. Anyone?-----------------Stephen |
|
|
|
|
Database Role Membership