June 14, 2005 at 9:48 am
Hi
Does anybody have a script that would generate a sql script to recreate logins, roles and users. i.e. to reproduce what you get when you right-click on a database in EM, select Generate SQL Script and select login, roles and users
I've managed to write some DMO to generate a script to recreate users, but I don't seem to be able to crack the roles and logins bit.
Any help appreciated
June 15, 2005 at 12:08 am
this should get you on track :
'script de database
Dim oDatabase As SQLDMO.Database2
Set oDatabase = New SQLDMO.Database2
'Get the database
Set oDatabase = globDMOCnn.Databases(strDatabaseNaam)
sScript = oDatabase.Script(bitmapScript1, , bitmapscript2)
WriteScriptPart (sScript)
'use db
WriteScriptPart (" use " & strDatabaseNaam & " -- gebruik userDB" & vbNewLine & "GO " & vbNewLine)
'Alles loggen ?
Logpunt ("Users")
WriteScriptPart ("-- MyApplicationName: Users & Logins --")
strStatementId = "ScriptDB - Users&Logins-" & strServerNaam & " - " & strDatabaseNaam
'script de Users
Dim oUser As SQLDMO.User
Dim oServerLogin As SQLDMO.Login
For Each oUser In oDatabase.Users
If Not oUser.SystemObject Then
'Serverlevel scripting
If oUser.Login = "" Then
WriteScriptPart ("-- MyApplicationName: user niet gescript wegens blanco login: " & oUser.Name)
Log (" MyApplicationName: user niet gescript wegens blanco login: " & oUser.Name & " ")
Else
Set oServerLogin = globDMOCnn.Logins(oUser.Login)
If Not oServerLogin.SystemObject Then
sScript = oServerLogin.Script
WriteScriptPart (sScript)
End If
'DatabaseLevel scripting
sScript = oUser.Script
WriteScriptPart (sScript)
End If
End If
Next
'script de DB-Roles
Dim oDBRole As SQLDMO.DatabaseRole
Dim strRoleMember As String
For Each oDBRole In oDatabase.DatabaseRoles
If oDBRole.AppRole = vbTrue Then
WriteScriptPart ("-- MyApplicationName: Opgelet ApplicationRole: " & oDBRole.Name & " ")
End If
If Not oDBRole.IsFixedRole Then
sScript = oDBRole.Script
WriteScriptPart (sScript)
End If
If oDBRole.Name <> "public" And oDBRole.AppRole = vbFalse Then
Set oQryResult = oDBRole.EnumDatabaseRoleMember
If oQryResult.Rows = 0 Then
Else
strStatementId = "ScriptDB - DBRoleMembers"
WriteScriptPart ("-- MyApplicationName: DBRoleMembers role: " & oDBRole.Name & " ")
strRoleMember = oQryResult.GetColumnString(1, 1)
For Ix = 1 To oQryResult.Rows
strRoleMember = oQryResult.GetColumnString(Ix, 1)
If UCase(strRoleMember) <> "DBO" Then
WriteScriptPart ("exec sp_addrolemember N'" & oDBRole.Name & "', N'" & strRoleMember & "'")
End If
Next
End If
End If
'WriteScriptPart (sScript)
Next
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 15, 2005 at 7:04 am
just what I wanted! Thankyou very much
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply