Presents your  
SQL SERVER E-NEWSLETTER for December 16, 2003 
 
<-------------------------------------------> 
  
DETERMINE WHETHER A USER BELONGS TO A ROLE   
  
Access to the functionality of a database often depends upon the rights 
of a given user. For example, managers may have the right to run certain 
queries or stored procedures, while users reporting to them do not. For a 
very small organization, you can grant specific rights to specific 
users, but this approach grows increasingly unwieldy as the number of
users increase. With even 50 users, this will be a maintenance nightmare.   
  
The tables containing the data of interest are sysusers and sysmembers. 
The former table contains data about both users and roles, and the two 
are distinguished by the column IsSQLRole, which contains 1 if the row 
denotes a role rather than a user. The following code lists all users
and roles:   
  
SELECT Member = Users.name, Role = Roles.Name   
FROM sysusers Users, sysusers Roles, sysmembers Members   
WHERE Roles.uid = Members.groupuid   
AND Roles.issqlrole = 1   
AND Users.uid = Members.memberuid   
ORDER BY 2, 1   
  
To list only the users who are members of a specified role, change the 
code to this:   
  
DECLARE @role varchar(100)   
SET @role = 'Managers'   
SELECT MemberName = Users.name, RoleName = Roles.Name   
FROM sysusers Users, sysusers Roles, sysmembers Members   
WHERE Roles.name = @role   
AND Roles.uid = Members.groupuid   
AND Roles.issqlrole = 1   
AND Users.uid = Members.memberuid   
ORDER BY 2, 1   
  
You might prefer to convert this code to a user-defined function (UDF), 
which returns a Boolean value, indicating whether the current user is a 
member of the role of interest. Make the @role variable a parameter and 
pass it in rather than defining it, as I've done above. From there, you can 
write your application code to call this function anytime you need to 
determine a given user's role.   
  
Arthur Fuller has been developing database applications for 20 years. 
His experience includes Access ADPs, Microsoft SQL 2000, MySQL, and .NET.   
----------------------------------------