Creating an integrated login for a Windows user group allows any user that is a member of the group to connect to the database without knowing a database user ID or password. There are two methods you can use to prevent a user who is a member of a Windows user group that has an integrated login from connecting to a database using the group integrated login:

You can use either of these methods to prevent members of Windows user groups from connecting to a database.

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.

Creating a procedure to prevent Windows users from connecting

The login_procedure option specifies a stored procedure to be called 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