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.
