SQLTeam.com | Weblogs | Forums

Effective Server Permissions for all Logins?

I can get the effective server permissions for a single login by doing something like:

EXECUTE AS LOGIN = 'test';
GO
SELECT * FROM fn_my_permissions(NULL, 'SERVER'); 
GO
REVERT;
GO

Is there a way I can query the effective server permissions for all the logins in [master].sys.server_principals?

I think you can just use a cursor over the list of logins. Declare a variable for the login name as sysname - then use:

EXECUTE AS LOGIN = @loginName;
SELECT * FROM fn_my_permissions(NULL, 'SERVER');
REVERT;

Okay Thanks. I was hoping to avoid a loop or dynamic sql but probably not possible.