Is there something like sp_removerolemember ?

• sufiyan.sarguroh

  Ten Centuries

  Points: 1264

  More actions

  February 29, 2012 at 8:34 am

  #253972

  Hi all ,

  I have a user in a database who has db_owner rights . I want to remove this db_owner right of the user on the database using t-sql script .

  GUI is quite easy , but the need for script is that i am planning to create a job to automate the same.

  Thanks in advance 🙂 .

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  February 29, 2012 at 8:43 am

  #1453211

  (At least) two ways of working this out:

  (1) Use Profiler to run a trace while you use the GUI, so that you can find out what commands were executed in the background.

  (2) Look in the Books Online topic sp_addrolemember for related topics to see whether the stored procedure to remove role members is there.

  Using one of those methods, you'll find that the proc you're looking for is called sp_droprolemember.

  John

• sufiyan.sarguroh

  Ten Centuries

  Points: 1264

  More actions

  March 1, 2012 at 3:36 am

  #1453518

  Done, Sp_droprolemember it is ! 🙂 Thanks . And the profiler solution was useful too , can be used for a variety of purposes.

  Cheers !