Finding Permission for user tables by Login

Sometimes, we need to find permissions of a SQL Server user or a Windows login on database tables. Doing so using the Object Explorer > Security > Logins or Object Explorer > [Database] > Security > Users in SSMS is quite tedious and hard to document. One simple way could be the use of the T-SQL system function, HAS_PERMS_BY_NAME. Let us say, I want to find out the permissions that a login called ReportLogin used in the data sources of all SSRS/Sharepoint reports has on the application tables  in a database called MyDB. This is what I do:

Use MyDB
go
execute as login = 'ReportLogin'
go
select SUSER_sNAME()
go
SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY');
SELECT DB_NAme() DatabaseName
      ,HAS_PERMS_BY_NAME (QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name), 'OBJECT', 'SELECT') AS have_SELECT
      ,HAS_PERMS_BY_NAME (QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name), 'OBJECT', 'INSERT') AS have_INSERT
      ,HAS_PERMS_BY_NAME (QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name), 'OBJECT', 'UPDATE') AS have_UPDATE
      ,HAS_PERMS_BY_NAME (QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name), 'OBJECT', 'DELETE') AS have_DELETE
      ,QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)  TABLE_NAME
      ,*
   FROM sys.objects where [type] in ('U')
go
revert
go
select SUSER_sNAME()
go

 

That does the following:

1. Executes the code as the SQL login called ReportLogin,
2. Checks if that login has any access to the database MyDB,
3. Gives the result as to the type of access (select, insert, update, delete) in the first four columns for each user table and
4. Then reverts back to my login.

 If it is a windows login whose access needs to be verified, simply substitute it as

Execute as Login = 'Domain\LoginName'

The rest of the code remains the same. Please note that this code doesn't work for Windows groups. That is because 'Execute As Login' doesn't work for Windows Groups. The next article would address this goal.

Possibilities:

1. The code above can be converted to an SP.
2. The SP can loop through differ logins in the syslogins catalog view.
3. For each login, it can insert data into a table.
4. Adding exec sp_MSforEachDB to execute that SP would execute it on all databases if DB_NAME() is added in the select list.
5. I would leave that portion to the reader.