none
SQL+SMO; how to get object level permission

    Question

  • Hello Experts,

    i wrote a little application to helps me to script the whole database with their tables;stored procedures; indexes; ...
    Here is my code

    Dim objServer As New Server("MyServer")
    
    Dim objDatabase As Database
    
    Dim objCollString As System.Collections.Specialized.StringCollection = Nothing
    
    
    
    Dim objFile As New FileStream("c:\Documents and Settings\USER\Desktop\CreateAlyceDatabaseByApp.sql", FileMode.Create, FileAccess.Write)
    Dim objFileWriter As New StreamWriter(objFile)
    objFileWriter.BaseStream.Seek(0, SeekOrigin.End)
    
            Try
                objServer.ConnectionContext.LoginSecure = True
                objServer.ConnectionContext.Connect()
                objDatabase = objServer.Databases("myDataBase")
                objCollString = objDatabase.Script()
    
                'SCRIPT(Database)
                For Each strScript As String In objCollString
                    objFileWriter.WriteLine(strScript)
                Next
                objFileWriter.WriteLine("GO")
                objFileWriter.WriteLine("USE [" & objDatabase.Name & "]")
                objFileWriter.WriteLine("GO")
    
                'SCRIPT SCHEMAS
                objFileWriter.WriteLine("-- SCHEMA SCRIPTS")
                For Each objschema As Schema In objDatabase.Schemas
                    If Not objschema.Name.StartsWith("db_") And _
                    objschema.Owner = "dbo" And _
                    objschema.Name <> "dbo" Then
                        objCollString = objschema.Script
                        For Each strScript As String In objCollString
                            objFileWriter.WriteLine(strScript)
                        Next
                        objFileWriter.WriteLine("GO")
                    End If
                Next
    
                'SCRIPT TABLES
                objFileWriter.WriteLine("-- TABLES SCRIPTS")
                For Each objTables As Table In objDatabase.Tables
                    objCollString = objTables.Script
                    For Each strScript As String In objCollString
                        objFileWriter.WriteLine(strScript)
                    Next
    
                    'SCRIPT INDEXES
                    For Each objIndex As Index In objTables.Indexes
                        objCollString = objIndex.Script
                        For Each strScript As String In objCollString
                            objFileWriter.WriteLine(strScript
                        Next
                    Next
    
    
                    'SCRIPT TRIGGER
                    For Each objTrigger As Trigger In objTables.Triggers
                        objCollString = objTrigger.Script
                        For Each strScript As String In objCollString
                            objFileWriter.WriteLine(strScript)
                        Next
                    Next
                Next
    
                'SCRIPT FOREIGN KEY
                objFileWriter.WriteLine("-- FOREIGN KEY SCRIPTS")
                For Each objTables As Table In objDatabase.Tables
                    'SCRIPT FOREIGN KEY
                    For Each objForeignKey As ForeignKey In objTables.ForeignKeys
                        objCollString = objForeignKey.Script
                        For Each strScript As String In objCollString
                            objFileWriter.WriteLine(strScript)
                        Next
                    Next
                Next
    
                'SCRIPT VIEWS
                objFileWriter.WriteLine("-- VIEWS SCRIPTS")
                For Each objView As View In objDatabase.Views
                    If objView.Schema.StartsWith("dbo") Then
                        objCollString = objView.Script
                        For Each strScript As String In objCollString
                            If strScript.StartsWith("SET") Then
                                objFileWriter.WriteLine(strScript)
                                objFileWriter.WriteLine("GO")
                            Else
                                objFileWriter.WriteLine(strScript)
                            End If
                        Next
                        objFileWriter.WriteLine("GO")
                    End If
                Next
    
                'SCRIPT STORED PROCEDURES
                objFileWriter.WriteLine("-- STORED PROCEDURES SCRIPTS")
                For Each objSP As StoredProcedure In objDatabase.StoredProcedures
                    If objSP.Schema.StartsWith("dbo") Then
                        objCollString = objSP.Script
                        For Each strScript As String In objCollString
                            If strScript.StartsWith("SET") Then
                                objFileWriter.WriteLine(strScript)
                                objFileWriter.WriteLine("GO")
                            Else
                                objFileWriter.WriteLine(strScript)
                            End If
                        Next
                        objFileWriter.WriteLine("GO")
                    End If
                Next
    
                'SCRIPT USERS
                objFileWriter.WriteLine("-- USER SCRIPTS")
                For Each objUser As User In objDatabase.Users
                    objCollString = objUser.Script
                    If objUser.DefaultSchema = "dbo" And _
                    objUser.Name <> "dbo" Then
                        For Each strScript As String In objCollString
                            If strScript.StartsWith("SET") Then
                                objFileWriter.WriteLine(strScript)
                                objFileWriter.WriteLine("GO")
                            Else
                                objFileWriter.WriteLine(strScript)
                            End If
                        Next
                        'SCRIPT USERS EXTENDEDPROPERTIES
                        For Each objExtendedProperty As ExtendedProperty In objUser.ExtendedProperties
                            objCollString = objExtendedProperty.Script
                            For Each strScript As String In objCollString
                                objFileWriter.WriteLine(strScript)
                            Next
                        Next
                    End If
                Next
    
                objFileWriter.WriteLine("-- EOF")
    
                MsgBox("Successfully done")
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                objServer.ConnectionContext.Disconnect()
                objFileWriter.Close()
            End Try

    The only thing I can not find out how I can do this is to script the users object level permission.
    Could someone help me please?

    Thanks!
    Alex

    Wednesday, March 03, 2010 7:02 AM

Answers

All replies