Find Permission on all objects

I came across a situation where I had to find all the stored procedures or user defined functions that do not have “execute” permission under a particular user. Here is a simple SQL Statement one which can be used under SQL Server 2005 to find those objects.

 Note: Make sure that you have logged in as a most privileged user through a Query Analyzer window and then replace the  ‘user_name’ for which you want to check permission. For example, following code will show all the procedures which do not have Execute permission for 'user_name'.

Declare @ObjName Varchar(100)

Create table #TempTable1 (Entity_Name varchar(100), Permission_Name varchar(25))

Create table #TempTable2 (Entity_Name varchar(100), Permission_Name varchar(25))

 

INSERT #TempTable1(Entity_Name)

SELECT Name FROM sys.objects

WHERE type='P'

ORDER by Name

 

EXECUTE AS USER = 'user_name';

 

Declare TableList cursor LOCAL FAST_FORWARD FOR     

SELECT Entity_Name FROM #TempTable1

ORDER by Entity_Name

OPEN TableList     

     

FETCH NEXT FROM TableList INTO @ObjName         

WHILE @@fetch_status = 0        

 BEGIN   

        INSERT INTO #TempTable2  

  SELECT Entity_Name,Permission_Name 

  FROM fn_my_permissions(@ObjName, 'OBJECT')

        FETCH NEXT FROM TableList INTO @ObjName   

 End

 

CLOSE TableList

DEALLOCATE TableList

 

SELECT Entity_Name FROM #TempTable1

WHERE Entity_Name NOT IN (SELECT Entity_Name FROM #TempTable2);

 

Drop table #TempTable1;

Drop table #TempTable2;

Speed Test: Generic List vs. ArrayList

It is very important to evaluate the most efficient way of passing data between layers. Many people recommend using ArrayList, some Generic List. But which one is the most efficient way to use? I thought to perform a speed test between Generic List and ArrayList in VS2005. Some people blindly accept that ArrayList is the fastest way. But is it true? Let’s have a real test by reading and writing hundred thousand records using each type and see the outcome. Knowing the fact that Collection, Data Table and XML is slower than these two but how much? Let’s put them to test too and compare the result.

 Following Windows From returns the result 

 

 From the above data, Generic List is a clear winner. The only downside is you have to write extra code for that. However, ArrayList takes the second place and relatively good with respect to Collection, Data Table or XML. So the decision is based on how efficient you want? You have to also realize the fact that in ArrayList, you will have 2 issues .First, you have to always retrieve data based on index (does not clearly tells you which column you are reading unless you know the mapping ). Second, you pay the penalty of Boxing/Unboxing, because every element in the ArrayList is an object, you will have to convert them to their actual data type. The benefit is you have to write less code and still achieve the better performance. 

 Following link gives more information and clear understanding on non generic and generic type and where to use what

http://blogs.msdn.com/kcwalina/archive/2005/09/23/Collections.aspx

Another important thing you must consider under 64 bit Operating System. Following is an article you can read.

http://blogs.msdn.com/joshwil/archive/2004/04/13/112598.aspx

Some of you asked as where is the code. I am dropping the code here, you can checkout on your own

Public Class FormSpeedTest

    Private Sub btnRun_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRun.Click

        btnRun.Enabled = False

        Call WriteArrayListData()

        Call WriteCustomerObjectData()

        Call WriteDataTableData()

        btnRun.Enabled = True

    End Sub

    Private Sub btnRead_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRead.Click

        btnRead.Enabled = False

        Call ReadArrayListData()

        Call ReadCustomerObjectData()

        Call ReadFromCustomerTable()

        Call ReadFromCustomerTableOptimization1()

        Call ReadFromCustomerTableOptimization2()

        'Call ReadXMLFromCustomerTable()

        btnRead.Enabled = True

    End Sub

    '/***********************ARRAY LIST Functionality ****************************************/

    Private Function WriteToArrayListByValue(ByVal custID As Int32, ByVal custName As String, ByVal custSex As Boolean, _

            ByVal custSalary As Decimal, ByVal custMaritalStatus As Char, ByVal custDOB As DateTime) As Object

        Dim arrCustomer As New ArrayList()

        arrCustomer.Add(custID)

        arrCustomer.Add(custName)

        arrCustomer.Add(custSex)

        arrCustomer.Add(custSalary)

        arrCustomer.Add(custMaritalStatus)

        arrCustomer.Add(custDOB)

        Return arrCustomer

    End Function

    Private Function WriteToArrayListFromArrayList(ByVal arrCust As ArrayList) As Object

        Dim arrCustomer As New ArrayList()

        arrCustomer.Add(arrCust(0))

        arrCustomer.Add(arrCust(1))

        arrCustomer.Add(arrCust(2))

        arrCustomer.Add(arrCust(3))

        arrCustomer.Add(arrCust(4))

        arrCustomer.Add(arrCust(5))

        Return arrCustomer

    End Function

    Private Sub WriteArrayListData()

        Dim stpWatch1 As New Diagnostics.Stopwatch

        Dim customerList As New ArrayList

        Dim customerObj As New List(Of SpeedTestCustomer)

        Dim s As Boolean = False

        Dim l As Decimal = CDec(1.278)

        Dim m As Char = CChar("S")

        Dim d As DateTime = #1/1/1901#

        stpWatch1.Start()

        For i As Integer = 0 To 1000000

            customerList.Add(WriteToArrayListByValue(i, "Customer Name " & i.ToString, s, l * i, m, d))

            If s Then s = False Else s = True

            If m = "S" Then m = CChar("M") Else m = CChar("S")

            d = DateAdd(DateInterval.Day, 1, d)

        Next

        stpWatch1.Stop()

        lblArrayListWriteMsg.Text = "Loading an ArrayList Object with " & customerList.Count.ToString & " Items. Total Duration : " & (stpWatch1.ElapsedMilliseconds / 1000).ToString & " Seconds."

    End Sub

    Private Sub ReadArrayListData()

        Dim stpWatch1 As New Diagnostics.Stopwatch

        Dim customerList As New ArrayList

        Dim custX As New ArrayList

        Dim s As Boolean = False

        Dim l As Decimal = CDec(1.278)

        Dim m As Char = CChar("S")

        Dim d As DateTime = #1/1/1901#

        '/*************LOAD FIRST ********************************/

        For i As Integer = 0 To 1000000

            customerList.Add(WriteToArrayListByValue(i, "Customer Name " & i.ToString, s, 1 * i, m, d))

            If s Then s = False Else s = True

            If m = "S" Then m = CChar("M") Else m = CChar("S")

            d = DateAdd(DateInterval.Day, 1, d)

        Next

        stpWatch1.Start()

        For i As Integer = 0 To customerList.Count - 1

            custX.Add(WriteToArrayListFromArrayList(CType(customerList(i), ArrayList)))

        Next

        stpWatch1.Start()

        lblArrayListReadMsg.Text = "Reading an ArrayList Object with " & customerList.Count.ToString & " Items. Total Duration : " & (stpWatch1.ElapsedMilliseconds / 1000).ToString & " Seconds."

    End Sub

    '/***********************Data Table Functionality ****************************************/

    Private Function CreateCustomerTable() As DataTable

        Dim dt As New DataTable("Customer")

        Dim dc1 As New DataColumn("CustId", GetType(Integer))

        Dim dc2 As New DataColumn("Name", GetType(String))

        Dim dc3 As New DataColumn("Sex", GetType(Boolean))

        Dim dc4 As New DataColumn("Salary", GetType(Decimal))

        Dim dc5 As New DataColumn("MaritalStatus", GetType(Char))

        Dim dc6 As New DataColumn("BirthDate", GetType(DateTime))

        dt.Columns.Add(dc1)

        dt.Columns.Add(dc2)

        dt.Columns.Add(dc3)

        dt.Columns.Add(dc4)

        dt.Columns.Add(dc5)

        dt.Columns.Add(dc6)

        Return dt

    End Function

    Private Sub WriteDataTableData()

        Dim stpWatch3 As New Diagnostics.Stopwatch

        Dim custTable As New DataTable

        custTable = CreateCustomerTable()

        Dim s As Boolean = False

        Dim l As Decimal = CDec(1.278)

        Dim m As Char = CChar("S")

        Dim d As DateTime = #1/1/1901#

        stpWatch3.Start()

        For i As Integer = 0 To 1000000

            Dim dr As DataRow = custTable.NewRow

            dr("CustID") = i

            dr("Name") = "Customer Name " & i.ToString

            dr("Sex") = s

            dr("Salary") = l * i

            dr("MaritalStatus") = m

            dr("BirthDate") = d

            custTable.Rows.Add(dr)

            If s Then s = False Else s = True

            If m = "S" Then m = CChar("M") Else m = CChar("S")

            d = DateAdd(DateInterval.Day, 1, d)

        Next

        stpWatch3.Stop()

        lblTableWriteMsg.Text = "Loading a Data Table Object with " & custTable.Rows.Count.ToString & " Items. Total Duration :" & (stpWatch3.ElapsedMilliseconds / 1000).ToString & " Seconds."

    End Sub

    Private Sub ReadFromCustomerTable()

        Dim stpWatch4 As New Diagnostics.Stopwatch

        Dim custTable As New DataTable

        custTable = CreateCustomerTable()

        Dim s As Boolean = False

        Dim l As Decimal = CDec(1.278)

        Dim m As Char = CChar("S")

        Dim d As DateTime = #1/1/1901#

        For i As Integer = 0 To 1000000

            Dim dr As DataRow = custTable.NewRow

            dr("CustID") = i

            dr("Name") = "Customer Name " & i.ToString

            dr("Sex") = s

            dr("Salary") = l * i

            dr("MaritalStatus") = m

            dr("BirthDate") = d

            custTable.Rows.Add(dr)

            If s Then s = False Else s = True

            If m = CChar("S") Then m = CChar("M") Else m = CChar("S")

            d = DateAdd(DateInterval.Day, 1, d)

        Next