Drop SQL Server Login when we delete SQL Databases

Question

question

TonyJK-1323 asked May 11, '22 NaomiNNN answered May 13, '22

Drop SQL Server Login when we delete SQL Databases

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

sql-server-general

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2022-05-13T02:54:55.957Z

NaomiNNN answered May 13, '22

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;

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-05-12T05:44:03.43Z

OlafHelper-2800 answered May 12, '22

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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 3 · 2022-05-12T08:23:55.34Z

Yufeishao-msft answered May 12, '22

Hi @TonyJK-1323,

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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 4 · 2022-05-12T20:57:24.807Z

ErlandSommarskog answered May 12, '22 TonyJK-1323 commented May 12, '22

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

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TonyJK-1323 · May 12 at 10:28 PM

Dear Erland and other fellows,

Many thanks for your advice. The script from Erland is very useful.

Is it possible to update the script so that it will return the results for all databases in the SQL Server ?

Thanks

0 ·

question