Russ.Johnsonville.net My take on databases

29Apr/140

Why is my database user disabled?

I recently had an issue where some databases that were created from a script the database user was disabled but in the system it was fine.  There is no way from the GUI to enable this.  After much searching I found my answer in an MSDN forum (http://social.msdn.microsoft.com/forums/sqlserver/en-US/5abd5b42-cd84-4fcc-9e3e-3dcac8814800/database-users-showing-disabled) where it was mentioned the CONNECT privilege is missing.  The command below will allow you to verify this is your issue replace the <username> with your effected username and run using the effected database:

SELECT * FROM sys.database_principals dprinc INNER JOIN sys.database_permissions dperm ON dprinc.principal_id = dperm.grantee_principal_id WHERE dprinc.name = '<username>'  AND dperm.permission_name = 'CONNECT'

The following script after replacing the <databasename> with your database name and <username> your effected username will fix this if the permission is missing:

/* Please replace the use <databasename> and the <username> */
USE < databasename >
GO

IF NOT EXISTS (
SELECT *
FROM sys.database_principals dprinc
INNER JOIN sys.database_permissions dperm ON dprinc.principal_id = dperm.grantee_principal_id
WHERE dprinc.NAME = '<username>'
AND dperm.permission_name = 'CONNECT'
)
BEGIN
GRANT CONNECT
TO [<username>]
END
GO

Share Button