Solved

Quickest way to script out SQL logins

Posted on 2002-06-18
3
242 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now