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

Scenario: How to check if the database user is enabled or disabled in multiple or all the databases in SQL Server

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. 

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 exits 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





1 comment: