How Can I Tell if a Windows Login has Rights to My Server?

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.

This entry was posted in Security, User Management, Utilities and tagged . Bookmark the permalink.

5 Responses to How Can I Tell if a Windows Login has Rights to My Server?

  1. Pingback: T-SQL Tuesday #005 - Self Service Performance Information | Adventures in SQL

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.