Who has access? – SQL Server

Home > Blogs > SQL Server > Who has access? – SQL Server

Who has access? – SQL Server

Like This Blog 0Jeff Jones
Added by July 11, 2007

Occasionally you would like to find out all the tables, views and stored procedures a user can access and what permissions they have.  With a combination of the new EXECUTE AS command and the new HAS_PERMS_BY_NAME function you can figure this out.  Below is a query that uses the sys.All_Objects metadata view to generate object names.  Then it passes the object names through the new function with some additional parameters (the securable type, and permission type) and it returns 1 if the user has that permission and 0 if they do not.

The result shows both implicit permissions granted through fixed server or database roles. It also shows explicit permission granted though user-defined roles or granted directly to the database user account.

The EXECUTE AS can only specify individual user principal defined to SQL Server.  It cannot reference a Windows Group.  This is only valid with SQL Server 2005.

EXECUTE AS LOGIN = 'miami\anders'

SELECT SCHEMA_NAME(schema_id) + '.' + name TableName
, type_desc
, HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name,
'OBJECT', 'SELECT') AS have_select
, HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name,
'OBJECT', 'UPDATE') AS have_update
, HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name,
'OBJECT', 'INSERT') AS have_insert
, HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name,
'OBJECT', 'DELETE') AS have_delete
, HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name,
'OBJECT', 'EXECUTE') AS have_execute
FROM sys.all_objects
ORDER BY type_desc, tablename

You may also like:  Microsoft White Paper - SQL Server 2016 and Windows Server 2016 Better Together


Videos You May Like

Creating Users and Managing Passwords in Microsoft Office 365

0 163 1

In this Office 365 training video, instructor Spike Xavier demonstrates how to create users and manage passwords in Office 365.

Windows 10 Features and Navigation – December 1, 2015

0 43 1

In this video, Windows Instructor Steve Fullmer presents the navigation and some of the new features associated with Windows 10 including Sysinternals Tools for Windows Client, Windows core concepts, exploring Process Explorer as well as some of the features that are not yet ready for prime time but will be released shortly in the future with Windows 10.

Detailed Forensic Investigation of Malware Infections – April 21, 2015

2 265 2

In this IT Security training video, Security expert Mike Danseglio (CISSP / CEH) will perform several malware investigations including rootkits, botnets, viruses, and browser toolbars.

Write a Comment

Share your thoughts...

Please fill out the comment form below to post a reply.