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.
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
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
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
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];
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.
Read more: http://www.mytechmantra.com/LearnSQLServer/How-to-Enable-Database-Users-in-SQL-Server/#ixzz3h4bCpsE4
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook
