February 5, 2003 at 1:03 pm
Is anyone aware of how to script roles and their associated users? What I would like is to have a script generated for EXEC sp_addrolemembers 'role','user'. I can generate it through ISQL, but would like to add it to our DMO scripting job. Is there a method or property that I am missing?
Thanks in advance.
Be great!
Michael
Be great!
Michael
February 5, 2003 at 5:11 pm
I dont see a way to do it directly - strange, probably there somewhere and Im just not seeing it! You can do it the hard way, something like the code below. If you're doing DMO, Lumigent is giving away a free DMO poster you might like (Im biased of course!).
Dim oserver As SQLDMO.SQLServer
Dim odb As SQLDMO.Database
Dim oRole As SQLDMO.DatabaseRole
Dim oResult As SQLDMO.QueryResults
Dim J As Integer
Dim K As Integer
'create standard server object first
Set oserver = New SQLDMO.SQLServer
With oserver
.LoginSecure = True
.Connect "eg\one"
End With
'use Northwind
Set odb = oserver.Databases("NORTHWIND")
'loop through roles and process any that has a member
For Each oRole In odb.DatabaseRoles
Set oResult = oRole.EnumDatabaseRoleMember
For J = 1 To oResult.Rows
For K = 1 To oResult.Columns
Debug.Print "sp_addrolemember '"; oRole.Name & "','" & oResult.GetColumnString(J, K) & "'"
Next
Next
Set oResult = Nothing
Next
Set odb = Nothing
oserver.DisConnect
Set oserver = Nothing
Andy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply