How to script Role Memebers

Is there any way to script Databse Role(s) and Role Memeber(s) using SQL-SMO?

The workaround i am using here is, set the server connection execution mode to CaptureSQL and and use AddMemeber method while looping through Server.Database.Roles.EnumMemebers. This creates a string collection of sp_addrolemembers. (let me know if someone wants to have a look at this code)

I couldn't even figureout how to do this using SQL Server Management Studion. When i script the entire database using SQL Server Mangement Studio, i dont see sp_addrolemember statements.

Am I missing something?

Try something like this:

Dim scrDBScript As Scripter
Dim objSMOObjects(100) As SqlSmoObject
Dim intObjCount As Integer
intObjCount = 0

Dim srv As Server
Dim srvConn As ServerConnection
srv = New Server("MyServer")
srvConn = srv.ConnectionContext
srvConn.LoginSecure = True

Dim db As Database
Dim objRoles As DatabaseRoleCollection
Dim objRole As DatabaseRole

db = srv.Databases("AdventureWorks")
objRoles = db.Roles
For Each objRole In objRoles
objSMOObjects(intObjCount) = objRole
intObjCount += 1
Next

scrDBScript = New Scripter(srv)
scrDBScript.Options.FileName = "c:\DBScript.sql"
scrDBScript.Options.IncludeHeaders = True
scrDBScript.Options.AppendToFile = True

ReDim Preserve objSMOObjects(intObjCount - 1)
scrDBScript.Script(objSMOObjects)

This code will create a script of each role defined in the AdventureWorks database. Hope that helps.

|||

Allen, Thanks for replying on this! However, this code only genrates sp_addrole statements. What I am looking for is to script rolememebers (sp_addrolememeber).

Any thoughts?

|||

Not sure if this works, but if it doesn't I think you'll get the idea:

db = srv.Databases("AdventureWorks")
objRoles = db.Roles
For Each objRole In objRoles
Dim colMembers As System.Collections.Specialized.StringCollection
Dim strMember As String
colMembers = objRole.EnumMembers
For Each strMember In colMembers
objSMOObjects(intObjCount) = objRole
intObjCount += 1
Next
Next

The EnumMembers method of the DatabaseRole object returns a string collection with the members of the role in the collection.