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
How to Build in a PSMethod to your PowerShell Code
In this video, PowerShell instructor Jason Yoder shows how to add Methods (PSMethod) to your code using free software that’s added into the PSObject. For instructor-led PowerShell courses, see our course schedule. Microsoft Windows PowerShell Training Download the Building Methods PowerShell script</a> used in this video. <# ╔══════════════════════════════════════════════════════════════════════════════╗ ║ ║ ║ Building Methods ║ ╟──────────────────────────────────────────────────────────────────────────────╢ … Continue reading How to Build in a PSMethod to your PowerShell Code
Configuring Windows Mobility Center and How to Turn it On and Off
Video transcription Steve Fullmer: In our Windows training courses, we often share information about the Windows 8.1 Mobility Center. Mobility Center was introduced for mobile and laptop devices in Windows 7. It’s present and somewhat enhanced in Windows 8. Since we don’t have mobile devices in our classrooms, I decided to take a little bit … Continue reading Configuring Windows Mobility Center and How to Turn it On and Off
OSPF Adjacency Troubleshooting Solution – Getting Close to the OSPF adj
In this video, Cisco CCNA & CCNP instructor Mark Jacob shows how to troubleshoot OSPF Adjacency issues by showing the distance between routers with the show ip ospf neighbor command.
Write a Comment
