Add Member to Role with wmi ?

• Marc-204254

  SSC Enthusiast

  Points: 170

  More actions

  February 1, 2005 at 6:11 am

  #87575

  How to add a win32 user defined in the sql server to a User defined role with WMI for sql server

  Guess MSSQL_MemberUser would do the trick, but how ? Can someone post a quick example

  Mydomain\User1 as username

  My_Role_Read as role name

  Thx

  Marc

• February 4, 2005 at 8:00 am

  This was removed by the editor as SPAM

• Marc-204254

  SSC Enthusiast

  Points: 170

  More actions

  February 14, 2005 at 9:27 am

  #542102

  Found the solution to my problem thought I'd share 

                                                       ' MyDB is the target DB

  objRoleName = "My_role_MyDB_r"        ' Role defined in MyDB

  ObjUserName = "NewUser"                 ' User defined in MyDB

  cmdobj = "sp_addrolemember "           ' in case I need looping with variables later on

  Set objLocator = CreateObject("wbemscripting.swbemlocator")

  ' Connect to correct NS

  Set ObjServices = ObjLocator.ConnectServer(".", "root\microsoftsqlserver")

  ' Integrated security version

  ObjServices.security_.impersonationlevel = 3

  ' What DB was that again

  set objService2 = objServices.Get("MSSQL_Database.SQLServerName='(LOCAL)',Name='MyDB'")

  ' Make the cmd usefull

  cmdobj = cmdobj & "'" & objRoleName & "', '" & ObjUserName & "'"

  ' Let us not wait anylonger do it

  objService2.executeimmediate(cmdobj)

  Hope this will help someone else

  Marc

   

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  February 15, 2005 at 3:05 am

  #542230

  You can also have a look here:

  http://www.microsoft.com/technet/community/scriptcenter/user/default.mspx

  Not sure if they have your script there, however this should save from reinventing the wheel anew, when it is unnecessary.

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]