sql server: get all the databases in which a login is being used

Question

I'm creating a few migration scripts to generate a sql server database from scratch.

In my '0005-create_login.up.sql' script I have something like:

CREATE LOGIN [mylogin] WITH PASSWORD=N'xxx=', 
GO

And in my '0005-create_login.down.sql'

DROP LOGIN [appws]
GO

Now I'd like to drop the login only if it is not being used in another database. I had a look at sp_helplogins code with:

sp_helptext 'sp_helplogins'

but it's really complex. I wonder if there's and easier way to get a login with the databases it is being used.

Answer 1

Check out my answer on a similar question. Code below will return a list of logins and a flag if they're currently mapped to a database user.

CREATE TABLE ##UnusedLogins (
    [Login] NVARCHAR(255),
    [InUse] BIT
)

INSERT INTO ##UnusedLogins ([Login], [InUse])
SELECT [login].[name] AS [Login],
    0 AS [InUse]
FROM sys.server_principals [login]
WHERE [login].[name] NOT LIKE 'NT SERVICE\%'
    AND [login].[name] NOT LIKE 'NT AUTHORITY\%'
    AND [login].[name] NOT LIKE '##%'
    AND [login].[type_desc] <> 'SERVER_ROLE'
    AND [login].[name] <> 'sa'

EXEC sp_msforeachdb 'UPDATE ul
SET [InUse] = 1
FROM ##UnusedLogins ul
INNER JOIN sys.server_principals [login] ON [login].[name] = ul.[Login]
INNER JOIN [?].sys.database_principals [user] ON [user].[sid] = [login].[sid]'

SELECT * FROM ##UnusedLogins

DROP TABLE ##UnusedLogins

Answer 2

Slightly different approach:

DECLARE @login sysname = N'mylogin';

SET NOCOUNT ON;

DECLARE @t TABLE(db nvarchar(255));

DECLARE @sql  nvarchar(max) = N'SELECT db = NULL',
        @base nvarchar(max) = N' UNION ALL SELECT PARSENAME(N''$qn$'',1)
    FROM $qn$.sys.database_principals AS dp WHERE EXISTS 
     (SELECT 1 FROM sys.server_principals AS sp
      WHERE sp.SID = dp.SID AND name = @login)';

SELECT @sql += REPLACE(@base,N'$qn$',QUOTENAME(name))
  FROM sys.databases WHERE state = 0;

INSERT @t(db) EXEC sys.sp_executesql @sql, N'@login sysname', @login;
DELETE @t WHERE db IS NULL;

IF EXISTS (SELECT 1 FROM @t)
BEGIN
  SELECT 'Not safe to drop. User is in these databases:';
  SELECT db FROM @t;
END
ELSE
BEGIN
  PRINT 'Safe to drop.';
END