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

Answer 1

Hi,

You have to use ScriptingOptions  and set ScriptingOptions.Permissions to get object level permissions in the script.
And then use Script(ScriptingOptions) method instead of Script() method.

If you are looking to script whole database or multiple objects you should take a look at Transfer.ScriptTransfer method

Regards,
Alok Parmesh

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 2

Hello Alok,

thanks a lot for your reply.
I'll test your recommendation and report my result.

Thanks!

Answer 3

Hello Amok,

thanks a lot. I used Transfer.ScriptTrasfer method to script my database.