SQL Server 2008 - Retrieving a Users’ Privileges for all tables in a databse

I recently answered a question on StackOverflow.com by a user who needed to retrieve a list of a given users Privileges for each Table in a given Database.

After some trial an error I ended up with the following T-SQL Script;

declare @Proc nvarchar(50)

declare @RowCnt int

declare @MaxRows int

declare @ExecSql nvarchar(255)

select @RowCnt = 1

select @Proc = 'SELECT * from fn_my_permissions'

declare @Import table (rownum int IDENTITY (1, 1) Primary key NOT NULL , TableName varchar(50))

insert into @Import (TableName) select name from sys.Tables

declare @Output table (entity_name varchar(50), subentity_name varchar(50), permission_name varchar(50))

select @MaxRows=count(*) from @Import

while @RowCnt <= @MaxRows

begin

    select @ExecSql = @Proc + '(N''' + TableName + ''', N''OBJECT'') where subentity_name = ''''' from @Import where rownum = @RowCnt 

    insert into @Output exec sp_executesql @ExecSql

    Select @RowCnt = @RowCnt + 1

end

select * from @Output

This will give the Permissions for the current user. If you want to find the permissions for a given user then use the following script;

EXECUTE AS LOGIN = N'username'

GO

declare @Proc nvarchar(50)

declare @RowCnt int

declare @MaxRows int

declare @ExecSql nvarchar(255)

select @RowCnt = 1

select @Proc = 'SELECT * from fn_my_permissions'

declare @Import table (rownum int IDENTITY (1, 1) Primary key NOT NULL , TableName varchar(50))

insert into @Import (TableName) select name from sys.Tables

declare @Output table (entity_name varchar(50), subentity_name varchar(50), permission_name varchar(50))

select @MaxRows=count(*) from @Import

while @RowCnt <= @MaxRows

begin

    select @ExecSql = @Proc + '(N''' + TableName + ''', N''OBJECT'') where subentity_name = ''''' from @Import where rownum = @RowCnt 

    insert into @Output exec sp_executesql @ExecSql

    Select @RowCnt = @RowCnt + 1

end

select * from @Output

GO

REVERT

GO

These scripts are based on;

fn_my_permissions procedure: 

This returns a list of permissions for the current user, for the given table in a database. Here we are executing the following code for each table in the database;

SELECT * from fn_my_permissions(N'tablename', N'OBJECT') where subentity_name = ''

More information can be found at http://msdn.microsoft.com/en-us/library/ms176097.aspx and http://books.google.co.uk/books?id=5_AEiJXbyiEC&pg=PA452&lpg=PA452&dq=tsql+HAS_perms_by_name+for+each+table&source=bl&ots=tSArrzcfa4&sig=HmPsF9vEFQC4g2hC8lWvCmJ8qao&hl=en&sa=X&ei=Nrr5UKvkCKLB0gXhtYD4Ag&ved=0CEEQ6AEwAg#v=onepage&q=tsql%20HAS_perms_by_name%20for%20each%20table&f=false and http://stackoverflow.com/a/497368/1305169

The sys.tables collection: 

This contains a list of tables for the given database. More information can be found at http://blog.sqlauthority.com/2007/06/26/sql-server-2005-list-all-tables-of-database/

A Simple Loop:

In order to loop through all of the tables, I modified a handy script I found here; http://weblogs.aspnet05.orcsweb.com/jgalloway/archive/2006/04/12/442618.aspx

Forcing html Input element to Numeric Only Input

While developing a shopping cart function for a client, I needed to restrict the user to entering numbers only in an Input field

I came across this handy post;

http://www.itjungles.com/javascript/how-to-use-javascript-to-force-numeric-value-in-textbox

Basically, we need to add the following function to the JavaScript section;

function isNumericKey(evt)

{

    var charCode = (evt.which) ? evt.which : event.keyCode

    if (charCode > 31 && (charCode < 48 || charCode > 57)) 

    {

        return false;

    }

    else

    {

        return true;

    }

}    

We then add the the following to your input element;

onkeypress="return isNumericKey(event)"

Hey Presto! You have a numeric only Input Element!

MVVM WPF Validating multiple items together

In an application I am developing, I required that two checkboxes be validated together, when either one of them were clicked. I also needed to only show the Validation error once in my Error Message Box.

To achieve this we first call the OnPropertyChanged routine, which in turn raises the PropertyChanged event, for each linked Property when any of the linked properties change.

For example;

Public Property Property1 As Boolean

    Get

        Return _Property1

    End Get

    Set(value As Boolean)

        If _Property1 <> value Then

            _Property1 = value

            OnPropertyChanged("Property1")

            OnPropertyChanged("Property2")

        End If

    End Set

End Property

For the Validation errors, I have employed a simple Key, Value Pair list to store all of the Validation Error Results. Thus, I simply check to see if the other Property has Registered an Error, and remove it if so;

        ''' <summary>

        ''' Validates the Current Item

        ''' </summary>

        ''' <param name="ColumnName"></param>

        ''' <returns></returns>

        ''' <remarks></remarks>

        Public Overrides Function ValidateItemValue(ColumnName As String) As String

            Dim strResult As String = ""

            Select Case ColumnName

                Case "Property1"

                    If Property1 = False And Property2 = False Then

                        strResult = "Please select either Property1, Propert2 or Both!"

                        If Me.lstErrors.ContainsKey("Property2") = True Then

                            Me.lstErrors.Remove("Property2")

                        End If

                    End If

                Case "Property2"

                    If Property1 = False And Property2 = False Then

                        strResult = "Please select either Property1, Property2 or Both!"

                        If Me.lstErrors.ContainsKey("Property1") = True Then

                            Me.lstErrors.Remove("Property1")

                        End If

                    End If

            End Select

            SortErrorList(ColumnName, strResult)        ' Add or Remove an Error Item

            Return strResult

        End Function