SQL Server: Tips and Tricks - 2 (2017)

How to check if User is Enabled or Disabled in all the databases in SQL Sever

Scenario:

You have created a login and then user in multiple databases by using that login. You need to take a look if the user is enable or disable in those databases.

Solution:

If the user is disabled in a database you will see a red arrow pointing downwards. I have disabled Aamir user as can be seen below by using

REVOKE CONNECT FROM Aamir

If you want to enable the user, you can use below statement

GRANT CONNECT TO UserName

But our goal is find out in all databases or in multiple database if the user is enabled or disabled. Also if the query does not return the Database name that means the user does not exist in that database.

--Provide the user name that you would like to check if enabled or Disabled in Databases

DECLARE @UserName VARCHAR(128)

SET @UserName='Aamir' -- I have provided Aamir as my User Name

IF OBJECT_ID('tempdb..##Temp_Table') IS NOT NULL

DROP TABLE ##Temp_Table

CREATE TABLE ##Temp_Table (

ServerName VARCHAR(1000),

DatabaseName VARCHAR(500)

,UserName VARCHAR(500)

,IsEnabled INT

)

DECLARE @SQLStatement VARCHAR(MAX)

SET @SQLStatement='Insert into ##Temp_Table

Select @@ServerName AS ServerName,DB_Name() AS DatabaseName,name, hasdbaccess

FROM sys.sysusers WHERE name='''''+@UserName+''''''

Print @SQLStatement

DECLARE @DatabaseName AS VARCHAR(500)

--DECLARE CURSOR

DECLARE CUR CURSOR

FOR

--Choose the DBs on which you would like to run the script

SELECT NAME

FROM sys.databases

WHERE database_id > 4

--OPEN CURSOR

OPEN CUR

--NEXT NEXT RECORD

FETCH NEXT

FROM CUR

INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @DBName AS NVARCHAR(500);

SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

--USE Dynamic SQL To Change DB name and run Check If user Enabled or Disabled in Database

EXEC (

N'USE ' + @DBName + N'; EXEC('''+@SQLStatement+''');'

);

FETCH NEXT

FROM CUR

INTO @DatabaseName

END

CLOSE CUR

DEALLOCATE CUR

Select * From ##Temp_Table