Solved

Quickest way to script out SQL logins

Posted on 2002-06-18
3
249 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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
Viewers will learn how the fundamental information of how to create a table.

623 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