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. Remus Rusanu says:

    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.

    • David Levy says:

      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.

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

  3. Remus Rusanu says:

    To get the NT groups a user is member of:

    execute as login = ‘domainuser’;
    select * from sys.login_token;

Leave a Reply

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