Sql-server – How to check if one specific login has any database users mapped on it

loginssql server

I've create some logins and database users. So I would like to accomplish the following.

Drop user from database:

USE [dbname]
GO
IF EXISTS (SELECT 1 FROM sysusers WHERE NAME = 'username')
    DROP USER [username]

Here I would like to check if the login has any database users in other databases and if not drop login:

USE [master]
GO
DROP LOGIN [username]
GO

Best Answer

use this query:

IF OBJECT_ID(N'tempdb..#results', 'U') IS NOT NULL 
    DROP TABLE #results;

CREATE TABLE #results(
    LoginName sysname
    ,LoginType nvarchar(60)
    ,IsMustChange bit
    ,DatabaseName sysname NULL
    ,DatabaseUserName sysname NULL
    ,DatabaseRoleName sysname NULL
    );

EXEC sp_MSforeachdb '
USE [?];
INSERT INTO #results
    SELECT
        sp.name AS LoginName
        ,sp.type_desc AS LoginType
        ,CAST(LOGINPROPERTY ( sp.name , ''IsMustChange'' ) AS bit) AS IsMustChange
        ,DB_NAME() AS DatabaseName
        ,dp.name AS DatabaseUserName
        ,r.name AS DatabaseRoleName
    FROM sys.server_principals sp
    LEFT JOIN sys.database_principals dp ON
        dp.sid = sp.sid
    LEFT JOIN sys.database_role_members drm ON
        drm.member_principal_id = dp.principal_id
    LEFT JOIN sys.database_principals r ON
        r.principal_id = drm.role_principal_id
    WHERE sp.name = ''SomeLogin''';

SELECT * FROM #results;

IF OBJECT_ID(N'tempdb..#results', 'U') IS NOT NULL 
    DROP TABLE #results;

Related Solutions

Sql-server – SQL Server drop and create all users in every DB

OK, there are several things that it could be but I'm not sure if we can determine which from here. These include the following:

AD propagation issues
AD caching "stickiness" on the target SQL Server
Domain Trust issues (particularly if the target server is in a different domain)
Windows Group membership/visibility issues
UAC blocking users access to certain groups needed for the new server
Kerberos issues (very common)
Etc...

And yes, dropping and re-adding the Users does seem to fix it in some of these cases, even though this should in theory never be needed because the SIDs should always match for Windows Users in transferred databases. It may be that the target SQL server simply lacks the same necessary server-level principal definitions as the original servers (and this as a side-effect creates explicit ones), or it may be that the act of adding the Login/user causes SQL Server to forcibly rerun the query and reload it's AD information for that user.

In any event, the real problem tends to be that dropping a user is a potentially disruptive and destructive act. It may work, but it may also cause some bad side-effects. Nonetheless, if you want to try that, then the best procs I know of for that are from Ted Krueger and they are located here: http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/fixing-orphaned-database-users.

Other (possible) solutions that you could employ include:

Automated procedure that does not drop the User, but does add and associate a server-level Windows Login for it.
Create a Windows AD group for access to the database, add all of the users to that, and then add that to the SQL Server as a Login and Database as a User. (this is the recommended approach for managing large-scale access anyway).
...

SQL Server – Query to List All Mapped Users for a Given Login

Here's one way using dynamic SQL. There's not really any way to do this without iterating, but this approach is much safer than undocumented, unsupported and buggy options like sp_MSforeachdb (background here and here).

This will get a list of all online databases, the mapped user (if it exists), along with the default schema name, and a comma-separated list of the roles they belong to.

DECLARE @name sysname = N'your login name'; -- input param, presumably

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'UNION ALL SELECT N''' + REPLACE(name,'''','''''') + ''',
    p.name                 COLLATE SQL_Latin1_General_CP1_CI_AS, 
    p.default_schema_name  COLLATE SQL_Latin1_General_CP1_CI_AS, 
    STUFF((SELECT N'','' + r.name 
      FROM ' + QUOTENAME(name) + N'.sys.database_principals AS r
      INNER JOIN ' + QUOTENAME(name) + N'.sys.database_role_members AS rm
      ON r.principal_id = rm.role_principal_id
      WHERE rm.member_principal_id = p.principal_id
      FOR XML PATH, TYPE).value(N''.[1]'',''nvarchar(max)''),1,1,N'''')
    FROM sys.server_principals AS sp
    LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
    ON sp.sid = p.sid
    WHERE sp.name = @name '
  FROM sys.databases WHERE [state] = 0;

SET @sql = STUFF(@sql, 1, 9, N'');

PRINT @sql;
EXEC master.sys.sp_executesql @sql, N'@name sysname', @name;

On more modern versions (2017+), I would still use dynamic SQL, but I would use STRING_AGG() instead of FOR XML PATH, probably something like this:

DECLARE @login sysname = N'your login name';

DECLARE @sql  nvarchar(max), 
        @sid  varbinary(85),
        @coll nvarchar(64) = N'COLLATE SQL_Latin1_General_CP1_CI_AS';

SELECT @sid = [sid] FROM sys.server_principals AS dp WHERE name = @login;

;WITH d AS 
(
  SELECT dbid = CONVERT(varchar(11), database_id),
         qn = QUOTENAME(name)
    FROM sys.databases WHERE [state] = 0
)
SELECT @sql = STRING_AGG(CONVERT(nvarchar(max),
        N'SELECT db = d.name, username = dp.name ' + @coll + ', 
        schemaname = dp.default_schema_name ' + @coll + ',
        roles = STRING_AGG(rp.name ' + @coll + ', N'','')
        FROM sys.databases AS d
        LEFT OUTER JOIN ' + qn + '.sys.database_principals AS dp ON dp.sid = @sid
        LEFT OUTER JOIN ' + qn + '.sys.database_role_members AS rm
        ON dp.principal_id = rm.member_principal_id
        LEFT OUTER JOIN ' + qn + '.sys.database_principals AS rp
        ON rp.principal_id = rm.role_principal_id
        WHERE d.database_id = ' + dbid + N'
        GROUP BY d.name, dp.name, dp.default_schema_name'
    ), char(13) + char(10) + N' UNION ALL ')
FROM d;

PRINT @sql;
EXEC master.sys.sp_executesql @sql, N'@sid varbinary(85)', @sid;

In this latter example, if you only want the databases with a user mapped to the named login, just change the first left join to an inner join.

Best Answer

Related Solutions

Sql-server – SQL Server drop and create all users in every DB

SQL Server – Query to List All Mapped Users for a Given Login

Related Question