Introduction
Here is another utility stored procedure that I use. This stored procedure will tell you how a user has access to your server. This is a great stored procedure to use for things like server dashboards where you only want people to see the dashboard for servers that they have access to without granting them all the rights that would go with the dashboard.
The Script
This stored procedure is quite simple. For a given login name call xp_logininfo, passing in the login name, piping the results into a temporary table. Once the table is built check to see if it has any rows and whether or not we should raise an exception based on the @hide_exceptions flag. If yes then raise an exception else just move along. Lastly, return any information returned about the login to the caller.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | CREATE PROCEDURE dbo.sp_dba_logininfo @loginame nvarchar(128), @hide_exceptions bit = 0 AS BEGIN CREATE TABLE #Logins ( account_name sysname NULL, type char(8) NULL, privilege char(9) NULL, mapped_login_name sysname NULL, permission_path sysname NULL ) INSERT #Logins EXEC xp_logininfo @loginame IF (SELECT COUNT(*) FROM #Logins) = 0 AND @hide_exceptions = 0 BEGIN RAISERROR('Specified user does not have access to this server.', 14, 1) END SELECT account_name, type, privilege, mapped_login_name, permission_path FROM #Logins DROP TABLE #Logins END GO |
Conclusion
As usual, I hope you find this stored procedure helpful. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.
xp_logininfo requires sysadmin membership. You can obtain the same information using:
execute as login = ‘domainuser’;
select has_perms_by_name(NULL, NULL, ‘CONNECT SQL’);
revert;
which only requires IMPERSONATE permission. Still a pretty powerful permission, but at least is an explicit grantable permission as opposed to the fixed role membership, let alone sysadmin membership.
Unfortunately there is no security function to check the permission of an arbitrary account. And interrogating the system catalogs (sys.server_principals and sys.server_permissions) is not enough since they don’t cover the NT group membership side of the problem.
I have to admit that I like the approach that you have outlined. It will not work for my needs because I actually need to get back what group a user is in but I could definitely see where someone might find it more useful.
As far as security goes I am not as worried about the internals of the stored procedure as I am about who is calling the stored procedure. Giving somebdoy a wide open window into your system to guess at logins until they find one that is an Admin could be trouble. I would secure this stored procedure to make sure that only a small handful of logins could run it.
To get the NT groups a user is member of:
execute as login = ‘domainuser’;
select * from sys.login_token;
I tried that query but it does not return any results for any of the users I tried.