To Get Role Name

• Hafiz Umar

  Old Hand

  Points: 355

  More actions

  October 12, 2006 at 1:41 am

  #174860

  I have setup roles and users for my database.  I have one department 'Marketing'.  There are 10-12 persons in that department.  The department is subdivided into three different groups based on their activities.  Say...Marketing1, Marketing2, Marketing3.  I have created three roles as Marketing1,Marketing2,Marketing3 and put the users in their respective rols and assigned the permission.  Everything worked fine so far.

  Now a new requirement has come. We have to develop an application in which all the marketing users will have the same permissions of read/write.  I created the another role with Marketing and put Marketing1,Marketing2,Marketing3 roles within that role. 

  Now in my front end application,which is Microsoft access 2003, I want to just retrieve the top level role name for the user which is Marketing in this case.

  I hope I make it clear. Please help.

• jfmccabe

  SSCrazy

  Points: 2547

  More actions

  October 13, 2006 at 1:43 am

  #665642

  Look at IS_MEMBER in BOL.

  Simplest way is to create a stored procedure like

  CREATE PROCEDURE Check

  @Check bit OUTPUT

  AS

  SET @Check = IS_MEMBER('Marketing ')

  GO

  Then call it from Access and you know if the user is in the Marketing Role.