Archive for December, 2013
Since SQL Server 2005 we’ve been able to grant one login the ability to impersonate another in order to have a different set of effective permissions. More than a few times I’ve found this paradigm useful in order to solve problems while adhering to the principal of least privilege and likewise more than a few times I’ve tried to find information about who has impersonation privileges in SSMS and have been severely disappointed to find that it is buried. This extremely simple script solves that problem and gives all of the information about who has impersonation rights on an instance.
If you want to read more about all the fantastic things you can do with EXECUTE AS and impersonation, MSDN is a good place to start
SELECT grantee_principal.name AS WhoCanImpersonate , grantee_principal.type_desc AS ImpersonatorType , sp.name AS WhoCanTheyImpersonate , sp.type_desc AS ImpersonateeLoginType FROM sys.server_permissions AS prmssn INNER JOIN sys.server_principals AS sp ON sp.principal_id = prmssn.major_id AND prmssn.class = 101 INNER JOIN sys.server_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id WHERE prmssn.state = 'G'