November 13, 2009 at 9:50 am
is there a way to determine if a windows/sql user belongs to a defined database role? We had something that worked in 2000 but no longer in 2005
November 13, 2009 at 9:58 am
Have you tried querying sys.database_role_members?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 13, 2009 at 10:03 am
...or sp_helprolemember?
Greg
November 13, 2009 at 10:24 am
How about:
IS_MEMBER ( { 'group' | 'role' } )
Arguments
' group '
Is the name of the Windows group that is being checked; must be in the format Domain\Group. group is sysname.
' role '
Is the name of the SQL Server role that is being checked. role is sysname and can include the database fixed roles or user-defined roles, but not server roles.
OR
IS_SRVROLEMEMBER ('role' [ , 'login' ] )
Arguments
' role '
Is the name of the server role that is being checked. role is sysname.
Valid values for role include the following:
sysadmin
dbcreator
bulkadmin
diskadmin
processadmin
serveradmin
setupadmin
securityadmin
' login '
Is the name of the SQL Server 2005 login to check. login is sysname, with a default of NULL. If no value is specified, the SQL Server login of the current user is used.
CEWII
November 13, 2009 at 11:37 am
I have these two little queries in my templates:
--select all roles for given user
select user_name(role_principal_id), user_name(member_principal_id) from sys.database_role_members
where user_name(member_principal_id) = 'user name'
--select all role members for given role
select user_name(role_principal_id), user_name(member_principal_id) from sys.database_role_members
where user_name(role_principal_id) = 'role name'
Regards
Piotr
...and your only reply is slàinte mhath
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply