Prevent Windows user groups members from connecting to a database

There are two methods you can use to prevent a user who is a member of a Windows user group with an integrated login from connecting to a database using the group integrated login:

Create an integrated login for the user to a database user ID that does not have a password.
Created a stored procedure that is called by the login_procedure option to check whether a user is allowed to log in, and raise an exception when a disallowed user tries to connect.

Creating an integrated login to a user ID with no password

When a user is a member of a Windows user group that has an integrated login, but also has an explicit integrated login for their user ID, the user's integrated login is used to connect to the database. To prevent a user from connecting to a database using their Windows user group integrated login, you can create an integrated login for the Windows user to a database user ID without a password. Database user IDs that do not have a password can not connect to a database.

To create an integrated login to a user ID with no password (SQL)

Add a user to the database without a password. For example:
CREATE USER db_user_no_password;

Create an integrated login for the Windows user that maps to the database user without a password. For example:

GRANT INTEGRATED LOGIN TO WindowsUser
AS USER db_user_no_password;

Creating a procedure to prevent Windows users from connecting

The login_procedure option specifies the stored procedure to call each time a connection to the database is attempted. By default, the dbo.sp_login_environment procedure is called. You can set the login_procedure option to call a procedure you have written that prevents specific users from connecting to the database.

The following example creates a procedure named login_check that is called by the login_procedure option. The login_check procedure checks the supplied user name against a list of users that are not allowed to connect to the database. If the supplied user name is found in the list, the connection fails. In this example, users named Joe, Harry, or Martha are not allowed to connect. If the user is not found in the list, the database connection proceeds as usual and calls the sp_login_environment procedure.

CREATE PROCEDURE DBA.user_login_check()
   BEGIN
      DECLARE INVALID_LOGON EXCEPTION FOR SQLSTATE '28000';
      // Disallow certain users
      IF( CURRENT USER IN ('Joe','Harry','Martha') ) THEN
        SIGNAL INVALID_LOGON;
      ELSE
         CALL sp_login_environment;
      END IF;
   END
go
GRANT EXECUTE ON DBA.user_login_check TO PUBLIC
go
SET OPTION PUBLIC.login_procedure='DBA.user_login_check'
go

Discuss this page in DocCommentXchange.