Quickest way to script out SQL logins

What's the best/quickest way to script out the logins on my producution server ? I'm looking to produce an SQL script file that I can run later via the query analyser. I see you can do this on a per database level but not for the entire server.

tia
gngAsked:
Who is Participating?
 
spcmnspffConnect With a Mentor Commented:
The SQLDMO object model has a .script method.  I've got some vbScrpt that utilizes this method in a DTS activex task to script out permissions periodically.  Maybe you can use it.  You just pont it to a server and it scripts out the permissions for any nonsystem, non-cheesy databases into a file.  The code below will script the grantdb access command, create the roles, an exec sp_addrolemember for each member. You will also need to set the path for the output file below.  


'**********************************************************************
'  Visual Basic ActiveX Script
'  Scipt all the user databases on a given server
'  Sean Peters - 05/24/02
'************************************************************************

' Set the server and script file path here . . .
Const Server = "SQLREPL"
' Set the path to a shared folder on the network
Const FilePath = "\\libertydc1\dfs\is folder\SQLScripts\"

Dim sql         'As Object
Dim db          'As Object
Dim intObjOpt   'As Long
Dim intObjOpt2  'As Long
Dim intDBOpt    'As Long
Dim strFolderPath 'As String
Dim fso                  'As Object    

Const Aliases = 16384
Const AppendToFile = 256
Const ClusteredIndexes = 8
Const DRI_AllConstraints = 520093696
Const NonClusteredIndexes = 8192
Const OwnerQualify = 262144
Const Permissions = 34
Const PrimaryObject = 4
Const ToFileOnly = 64
Const Triggers = 16
Const SevenOnly = 16777216

Function Main()
     Dim DBase

      Set sql = CreateObject("SQLDMO.SQLServer")
      Set db = CreateObject("SQLDMO.Database")
   
     strFolderPath = FilePath & "\" & Server & "\"

      'Create a file system object and check for the folder
      Set fso=CreateObject("Scripting.FileSystemObject")
      If Not fso.FolderExists(strFolderPath) Then
            fso.CreateFolder(strFolderPath)
      End If

     'Set the scripting options.
     'Options for each object
     intObjOpt = ClusteredIndexes Or _
                  NonClusteredIndexes Or _
                  OwnerQualify Or _
                  PrimaryObject Or _
                  Triggers Or _
                  ToFileOnly Or _
                  AppendToFile Or _
                  Aliases Or _
                  DRI_AllConstraints Or _
                  Permissions
     
     'Options2 for each object
     intObjOpt2 = SevenOnly
     
     'Options for the database
     intDbopt = PrimaryObject Or _
                  Permissions Or _
                  ToFileOnly
     
     'Connect to the server
     sql.Connect server, "UID", "PWD"
   
     For Each DBase In sql.Databases
         If DBase.Name <> "Master" And _
         DBase.Name <> "MSDB" And _
         DBase.Name <> "TempDB" And _
         DBase.Name <> "Northwind" And _
         DBase.Name <> "Pubs" Then
            Set db = DBase
            ScriptDB
         End If
     Next

      set sql = nothing
      set db = nothing
      Set fso = Nothing
      Main = DTSTaskExecResult_Success
End Function

Sub ScriptDB()
     Dim genObj
     Dim results    'As QueryResults
     Dim strTemp    'As String
     Dim j          'As Integer
     Dim ts    'As Object
     Dim strFilePath 'As String    
     
   
     strFilePath = strFolderPath & db.Name & "_" & fExportDate & ".sql"
     
     ' Script Database and permisions here first
     db.Script intDBOpt, strFilePath, intObjOpt2
   
      'Script Database users
     For Each genObj In db.Users
        genObj.Script intObjOpt, strFilePath, intObjOpt2
     Next
     
     'Script nonfixed dbroles and
     'Script Members for all roles
     For Each genObj In db.DatabaseRoles
        If genObj.IsFixedRole = False Then
            genObj.Script intObjOpt, strFilePath, intObjOpt2
        End If
        If genObj.Name <> "db_owner" And genObj.Name <> "public" Then
            Set results = genObj.EnumDatabaseRoleMember
            strTemp = ""
            For j = 1 To results.Rows
                strTemp = strTemp & "exec sp_addrolemember N'" & genObj.Name & _
                "', N'" & results.GetColumnString(j, 1) & "'" & _
                Chr(13) & Chr(10) & "GO" & Chr(13) & Chr(10)
            Next
            Set results = Nothing
            If Len(strTemp) > 0 Then
                'Append to the script file
         set ts=fso.OpenTextFile(strFilePath,8,true)
         ts.WriteLine strTemp
         ts.Close
         set ts=Nothing
            End If
        End If
     Next
 End Sub
0
 
pivarCommented:
Hi,

Which info are you lookin for? You can script the server logins if you check "Script SQL server logins" on the options tab. If you like to have scripting in EM for all users in all databases, I think you have do it per database.

/pivar
0
 
spcmnspffCommented:
Here's the mod to do one database at a time . . .


'**********************************************************************
'  Visual Basic ActiveX Script
'  Scipt all the user databases on a given server
'  Sean Peters - 05/24/02
'************************************************************************

' Set the server and script file path here . . .
Const Server = "SQLREPL"
' Set the path to a shared folder on the network
Const FilePath = "\\libertydc1\dfs\is folder\SQLScripts\"

Dim sql         'As Object
Dim db          'As Object
Dim intObjOpt   'As Long
Dim intObjOpt2  'As Long
Dim intDBOpt    'As Long
Dim strFolderPath 'As String
Dim fso                  'As Object    

Const Aliases = 16384
Const AppendToFile = 256
Const ClusteredIndexes = 8
Const DRI_AllConstraints = 520093696
Const NonClusteredIndexes = 8192
Const OwnerQualify = 262144
Const Permissions = 34
Const PrimaryObject = 4
Const ToFileOnly = 64
Const Triggers = 16
Const SevenOnly = 16777216

Function Main()
     Dim DBase
     Dim dbName   'As String
     
      dbName = "MyDatabase"
      Set sql = CreateObject("SQLDMO.SQLServer")
      Set db = CreateObject("SQLDMO.Database")
   
      strFolderPath = FilePath & "\" & Server & "\" & dbName & "\"

      'Create a file system object and check for the folder
      Set fso=CreateObject("Scripting.FileSystemObject")
      If Not fso.FolderExists(strFolderPath) Then
            fso.CreateFolder(strFolderPath)
      End If

     'Set the scripting options.
     'Options for each object
     intObjOpt = ClusteredIndexes Or _
                  NonClusteredIndexes Or _
                  OwnerQualify Or _
                  PrimaryObject Or _
                  Triggers Or _
                  ToFileOnly Or _
                  AppendToFile Or _
                  Aliases Or _
                  DRI_AllConstraints Or _
                  Permissions
     
     'Options2 for each object
     intObjOpt2 = SevenOnly
     
     'Options for the database
     intDbopt = PrimaryObject Or _
                  Permissions Or _
                  ToFileOnly
     
     'Connect to the server
     sql.Connect server, "UID", "PWD"
   
     Set db = sql.DataBases(dbName)
     ScriptDB
     
     'For Each DBase In sql.Databases
     '    If DBase.Name <> "Master" And _
     '    DBase.Name <> "MSDB" And _
     '    DBase.Name <> "TempDB" And _
     '    DBase.Name <> "Northwind" And _
     '    DBase.Name <> "Pubs" Then
     '       Set db = DBase
     '       ScriptDB
     '    End If
     'Next

      set sql = nothing
      set db = nothing
      Set fso = Nothing
      Main = DTSTaskExecResult_Success
End Function

Sub ScriptDB()
     Dim genObj
     Dim results    'As QueryResults
     Dim strTemp    'As String
     Dim j          'As Integer
     Dim ts    'As Object
     Dim strFilePath 'As String    
     
   
     strFilePath = strFolderPath & db.Name & "_" & fExportDate & ".sql"
     
     ' Script Database and permisions here first
     db.Script intDBOpt, strFilePath, intObjOpt2
   
      'Script Database users
     For Each genObj In db.Users
        genObj.Script intObjOpt, strFilePath, intObjOpt2
     Next
     
     'Script nonfixed dbroles and
     'Script Members for all roles
     For Each genObj In db.DatabaseRoles
        If genObj.IsFixedRole = False Then
            genObj.Script intObjOpt, strFilePath, intObjOpt2
        End If
        If genObj.Name <> "db_owner" And genObj.Name <> "public" Then
            Set results = genObj.EnumDatabaseRoleMember
            strTemp = ""
            For j = 1 To results.Rows
                strTemp = strTemp & "exec sp_addrolemember N'" & genObj.Name & _
                "', N'" & results.GetColumnString(j, 1) & "'" & _
                Chr(13) & Chr(10) & "GO" & Chr(13) & Chr(10)
            Next
            Set results = Nothing
            If Len(strTemp) > 0 Then
                'Append to the script file
         set ts=fso.OpenTextFile(strFilePath,8,true)
         ts.WriteLine strTemp
         ts.Close
         set ts=Nothing
            End If
        End If
     Next
 End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.