How to reverse engineer role membership

• devvw

  SSC Enthusiast

  Points: 133

  More actions

  June 4, 2003 at 8:59 am

  #80346

  Hi,

  I'm currently writing a SQL-DMO application that is reverse engineering all objects on a given server (DBs, Tables, views, users, .....).(Lot of fun with SQL-DMO 😉 )

  But I do have a problem while trying to script the role membership of the logins or users.

  I already can script the logins created on the server... but without server role info.

  I can also script the databases users and the given object permissions.... but not the database roles (db_datareader,...)

  My goal would be to produce something like what you can do by using the 'Generate SQL script' feature of SQL EM (Launch application, don't choose any objects and simply check the box "script database users & database roles" in the "options" tab)

  Can someone please put me on the track ?

  Thanks in advance.

  Igor

• Jurgen H.

  Say Hey Kid

  Points: 683

  More actions

  June 6, 2003 at 1:29 am

  #460103

  try this

  oserver.databases("dbname").DatabaseRoles("rolname").Script

  you could use this in a loop

• devvw

  SSC Enthusiast

  Points: 133

  More actions

  June 6, 2003 at 1:38 am

  #460104

  Thanks Jurgen.

  But if I'm not wrong this command will script the roles : sp_addrole.....

  The kind of script I would like to obtain would look like :

  if not exists (select * from dbo.sysusers where name = N'xxxxxx' and uid < 16382)

  EXEC sp_grantdbaccess N'xxxxxx', N'xxxxxx'

  GO

  exec sp_addrolemember N'db_datareader', N'xxxxxx'

  GO

  exec sp_addrolemember N'db_datawriter', N'xxxxxx'

  GO

  No problem to get the script of sp_grantdbaccess. The trick is to get the "sp_addrolemember".

  Regards

  Igor

• Jurgen H.

  Say Hey Kid

  Points: 683

  More actions

  June 6, 2003 at 2:20 am

  #460111

  I gues you have to write that part yourself by using eg

  oserver.Databases("dbname").DatabaseRoles("rolname").EnumDatabaseRoleMember

  This returns a queryresult which can be easily read (loop through rows).

  Regards,

  Jurgen