Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Quickest way to script out SQL logins

Posted on 2002-06-18
3
Medium Priority
?
254 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 400 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

722 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