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

73 Views Asked by At

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?

1

There are 1 best solutions below

0
J. Mini On BEST ANSWER

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;