0

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.

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
  • 1
    Please be aware sp_msforeachdb is undocumented, unsupported, and severely broken. See here, here, and here for better alternatives. – Aaron Bertrand Apr 11 at 1:24
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.