Who has access? – SQL Server
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
WHERE type_desc IN ('USER_TABLE', 'SQL_STORED_PROCEDURE', 'VIEW')
AND SCHEMA_NAME(schema_id) NOT IN ('sys', 'INFORMATION_SCHEMA')
ORDER BY type_desc, tablename
REVERT
Agile Methodology in Project Management
In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management
An Overview of Office 365 – Administration Portal and Admin Center
This is part 1 of our 5-part Office 365 free training course. In this Office 365 training video, instructor Spike Xavier introduces some of the most popular services found in Microsoft Office 365 including the Admin Portal and Admin Center. For instructor-led Office 365 training classes, see our course schedule: Spike Xavier SharePoint Instructor – … Continue reading An Overview of Office 365 – Administration Portal and Admin Center
Creating Users and Managing Passwords in Microsoft Office 365
In this Office 365 training video, instructor Spike Xavier demonstrates how to create users and manage passwords in Office 365. For instructor-led Office 365 training classes, see our course schedulle: Spike Xavier SharePoint Instructor – Interface Technical Training Phoenix, AZ 20347: Enabling and Managing Office 365
Write a Comment
