Search code examples
sql-servert-sqlpermissionsssmswindows-users

What script does SSMS run when asking for the 'Effective' items on the 'Permissions' screen for a table?


Suppose that I want to see who has permissions on a table in my database. In my ignorance, I trust the GUI to give me all of the answers. I therefore open up the database in SSMS and navigate to the table I desire. I right-click on it and go to "Properties". From here, I navigate to "Permissions". I see that this requires me to specify a user, so I specify a Windows user. Let's call them FooCorp\x12345. On the 'Explicit' menu, I see that they have no permissions at all. I therefore click on the 'Effective' menu and I get this error.

Could not obtain information about windows NT group/user 'FooCorp\x12345', error code 0x5

I really don't care about fixing this error. Instead, I want to know this: What procedure, script, or other object was used by SSMS to (fail to) fetch the 'Effective' permissions?


Solution

  • Several comments correctly suggested checking the SQL Server Profiler. It reports that the script is:

    EXECUTE AS LOGIN = N'FooCorp\x12345';
    SELECT
        permission_name AS [Permission]
        ,subentity_name as [Column]
    FROM fn_my_permissions(N'[dbo].[tblTest]', N'OBJECT')
    ORDER BY permission_name, subentity_name;
    REVERT;