How to Enable Database Users in SQL Server?

How to Enable Database Users in SQL Server:

Read more: http://www.mytechmantra.com/LearnSQLServer/How-to-Enable-Database-Users-in-SQL-Server/#ixzz3cjshe4Wa 

Introduction

During a recent Production Database refresh we encountered a strange problem where all the Windows Groups/Users along with SQL Server Users within the Database were disabled.

When you connect to SQL Server Using System Administrator (SA) permissions and expand User Database -> Security -> Users you would find all of the Windows Groups/Users and SQL Server Users being disabled. A disabled user will have a small red down arrow icon against the user. However, you cannot enable or disable the database users using SQL Server Management Studio. 

You will receive the below mentioned error message when trying to connect to the database.

Error Message

The database AdventureWorks2012 is not accessible.

This issue generally happens when CONNECT permission to the user is disabled for the database.

Follow the steps mentioned below to identify and fix this issue.

Step1: Query to list all users within the database

/*
    Query to list all users within the database

    S = SQL_USER
    U = WINDOWS_USER
    G = WINDOWS_GROUP
*/

Use AdventureWorks
GO

SELECT
    principal_id AS [Principal ID]
    ,name AS [Database User Name]
    ,type AS [User Type]
    ,type_desc AS [User Type Description]
FROM sys.database_principals
    WHERE TYPE IN ('G','U','S')
        ORDER BY type ASC
GO

Step 2: Find all users within the database which are disabled.

You can find all the users within the database which are disabled either by using SSMS or using TSQL.

The below query will find all the Windows Groups/users and SQL Users which are in disabled state within the user database.

/*
Find all users within the database which are disabled.

    hasdbaccess = 0 Means User has no access
    hasdbaccess = 1 Means User has access
*/

USE AdventureWorks
GO

SELECT
    SU.NAME
    ,DP.principal_id
    ,dp.type
    ,dp.type_desc
    ,su.hasdbaccess
FROM sys.database_principals DP
INNER JOIN sys.sysusers SU ON dp.principal_id = SU.uid
    WHERE DP.TYPE IN ('G','U','S')
AND SU.hasdbaccess = 0
    ORDER BY DP.TYPE ASC
GO

 

A disabled user will have a red arrow point downwards as seen in the below snippet.

 

Step 3: Generate Script to enable all Users who are disabled within the user database

/*
Generate Script to Grant Connect to Database Users

U = WINDOWS_USER
G = WINDOWS_GROUP

*/

Use AdventureWorks
GO

SELECT 'GRANT CONNECT TO [' + SU.name + '];' FROM sys.database_principals DP
INNER JOIN sys.sysusers SU ON dp.principal_id = SU.uid
WHERE DP.TYPE IN ('G','U')
AND SU.hasdbaccess = 1
GO

Sample Output

GRANT CONNECT TO [Domain\User1];
GRANT CONNECT TO [Domain\AdminGroup];

Open a New Query window and run the above script under the context of the database to GRANT CONNECT to user and to resolve the issue.