Drop SQL Server Login when we delete SQL Databases

Question

We are going to delete some unused SQL Server Databases. However, there is a requirement to remove all associated SQL Server Login.

Is there any easy way for us to remove corresponding SQL Login when we delete unused database ? OR can we get a list of Database users and their corresponding SQL Server Login so that we can update manually ?

Thanks

Answer 1

Try this code:

DECLARE @t TABLE
    (
        [Server Login] VARCHAR(200)
        , [DB User] VARCHAR(200)
        , default_database_name VARCHAR(200)
        , dbName VARCHAR(200)
    );



DECLARE
    @rows INT
    , @row INT = 1
    , @dbName sysname
    , @sql VARCHAR(MAX);
DECLARE @dbS TABLE (id INT IDENTITY PRIMARY KEY, dbname sysname);
INSERT INTO @dbS (dbname) SELECT name FROM sys.databases ORDER BY name;
SET @rows = @@ROWCOUNT;
WHILE @row <= @rows
    BEGIN

        SELECT @dbName = dbname FROM @dbS WHERE id = @row;
    --  SET @sql = 'USE ' + QUOTENAME(@dbName);
        BEGIN TRY
        --  EXECUTE (@sql);
            SET @sql = 'SELECT
                sp.name AS [Server login]
                , dp.name AS [DB user]
                , sp.default_database_name
                , ' + QUOTENAME(@dbName, '''') + '
            FROM ' + QUOTENAME(@dbName) + '
                .sys.server_principals sp
                JOIN '  + QUOTENAME(@dbName) + '.sys.database_principals dp
                    ON sp.sid = dp.sid
            ORDER BY
                sp.name;'

             INSERT @t
             EXECUTE (@SQL);    

        END TRY
        BEGIN CATCH
            PRINT ERROR_MESSAGE();
        END CATCH;
        SET @row = @row + 1;
    END;

SELECT * FROM @t;

Answer 2

Is there any easy way for us to remove corresponding SQL Login when we delete unused database

There is no build-in function for it.

OR can we get a list of Database users and their corresponding SQL Server Login so that we can update manually ?

Yes, you have to do it manually.
In SSMS open database => Security, there you see all assigned database users. Then you have to check on server level => Security the login if it's associated to other databases before you drop the login.

Answer 3

Hi @TonyJK ,

No way to remove all related SQL Server logins, but finish manually
You can view the mapping in the Login properties
https://dba.stackexchange.com/questions/81595/a-query-that-lists-all-mapped-users-for-a-given-login

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

This query lists all database users and their associated logins. The next step, to see if any of these logins are present in other database requires that you visit them all; there is no single query for this. Best is to loop over all databases, and see how many logins that map.

SELECT sp.name AS [Server login], dp.name AS [DB user]
FROM   sys.server_principals sp
JOIN   sys.database_principals dp ON sp.sid = dp.sid
ORDER BY sp.name