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.
