IS_Member

IS_Member

• TMB

  It may or may not be a good choice. SETUSER is on the deprecated list, and is also not available to 'normal' users.

  --- quote BOL ---
  Important SETUSER is included in MicrosoftR SQL Server 2000 only for backward compatibility, and its usage is not recommended. SETUSER may not be supported in a future release of SQL Server.
  
  

  Permissions

  SETUSER permissions default to members of the sysadmin fixed server role and are not transferable.
  --- end quote ---
  
  /Kenneth

  
  

• Sayed Zeeshan

  I don't think there's any alternative.

  If you think about it, it wouldn't be too safe if just anyone could go SETUSER <someonelse> and then do stuff with another identity. This is why it's for sysadmins only.

  For your other question, the role cannot be a server role. It has to be a database fixed role or a user-defined role.

  /Kenneth

  
  

• ryandailey21

  Then what is the alternate

  Also If I create a database role and add some Windows groups to that role. Is_Member('rolename') always return 0.

  Is this something I am doing wrong

  
  
  

• Natural_orange

  You can use SETUSER

   

  Here is a small example

  --Create Dilbert user

  EXEC sp_addlogin 'Dilbert', 'food', 'pubs'
  EXEC sp_adduser 'Dilbert'

  --This is my login

  select IS_MEMBER ('db_owner') --will be 1

  
  -- let's run the select as Dilbert
  SETUSER 'Dilbert'

  select IS_MEMBER ('db_owner')
   -- will be 0

  --change back to  self
  SETUSER

  --remove Dilbert
  EXEC sp_dropuser 'Dilbert'
  EXEC sp_droplogin 'Dilbert'

   

  Denis the SQL Menace

  http://sqlservercode.blogspot.com/

  
  
• IS_Member