More notes for the 70-462 exam. This time we’re showing examples from ALTER LOGIN to enable and disable logins, as well a denying and granting the connect permission.

To Deny connect and disable a sql login…

USE [master]
GO
DENY CONNECT SQL TO [sql_user_c]
GO
ALTER LOGIN [sql_user_c] DISABLE
GO

Grant connect and enable a sql login

USE [master]
GO
GRANT CONNECT SQL TO [sql_user_c]
GO
ALTER LOGIN [sql_user_c] ENABLE
GO

To deny connect and disable a Windows login…

USE [master]
GO
DENY CONNECT SQL TO [SQL-A\local_account_b]
GO
ALTER LOGIN [SQL-A\local_account_b] DISABLE
GO

To grant connect and enable a Windows login…

USE [master]
GO
GRANT CONNECT SQL TO [SQLA\local_account_b]
GO
ALTER LOGIN [SQLA\local_account_b] ENABLE
GO

To deny connect to a windows domain group

USE [master]
GO
DENY CONNECT SQL TO [DOMAIN\domain_group_b]
GO

This doesn’t work for a windows group buy denying connect is essentially the same thing…

ALTER LOGIN [DOMAIN\domain_group_b] DISABLE
GO

This statement will return the following error…

Msg 33129, Level 16, State 1, Line 1
Cannot use ALTER LOGIN with the ENABLE or DISABLE argument for a Windows group. GRANT or REVOKE the CONNECT SQL permission instead.