SQLSENSE: SQL Server Practical Approaches
SQLSENSE - SQL situations and their Solutions - Suresh Kumar Maganti
Tuesday, June 20, 2017
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:
execute as login = 'ReportLogin'
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')
That does the following:
Executes the code as the SQL login called ReportLogin,
Checks if that login has any access to the database MyDB,
Gives the result as to the type of access (select, insert, update, delete) in the first four columns for each user table and
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.
The code above can be converted to an SP.
The SP can loop through differ logins in the syslogins catalog view.
For each login, it can insert data into a table.
Adding exec sp_MSforEachDB to execute that SP would execute it on all databases if DB_NAME() is added in the select list.
I would leave that portion to the reader.
Suresh Kumar Maganti
Links to this post
Share to Twitter
Share to Facebook
Share to Pinterest