Solved

Quickest way to script out SQL logins

Posted on 2002-06-18
3
244 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:gng
  • 2
3 Comments
 
LVL 22

Expert Comment

by:pivar
ID: 7088905
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
 
LVL 5

Accepted Solution

by:
spcmnspff earned 100 total points
ID: 7089078
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
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7089172
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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question