MS SQL server request to withdraw all users

Question

Hello everyone!
Please help me make a request in MS SQL Server
It is necessary to display all accounts, sql and domain, which will display the fields account name, conestion string, database name, Usermapingdatabase (screenshot 2) and server role (screenshot 3)
an example of how it should look for one account (screenshot1)

Answer 1

Please check if the solutions provided in the following question solve your need:

https://dba.stackexchange.com/questions/81595/a-query-that-lists-all-mapped-users-for-a-given-login

Answer 2

Unfortunately, not one of the examples on your link either does not work, or produces emptiness, or does not indicate data.

Example 1)

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;  

Here is the result:

Example 2)
In this option, you need to enter the account name, I need for all accounts for both SQL and domain accounts. app.
and also for some reason does not want to work:

SET NOCOUNT ON  
    CREATE TABLE #temp1  
        (  
          SERVER_name SYSNAME NULL ,  
          Database_name SYSNAME NULL ,  
          UserName SYSNAME ,  
          GroupName SYSNAME ,  
          LoginName SYSNAME NULL ,  
          DefDBName SYSNAME NULL ,  
          DefSchemaName SYSNAME NULL ,  
          UserID INT ,  
          [SID] VARBINARY(85)  
        )  
  
    DECLARE @command VARCHAR(MAX)  
    --this will contain all the databases (and their sizes!)  
    --on a server  
    DECLARE @databases TABLE  
        (  
          Database_name VARCHAR(128) ,  
          Database_size INT ,  
          remarks VARCHAR(255)  
        )  
    INSERT  INTO @databases--stock the table with the list of databases  
            EXEC sp_databases  
  
    SELECT  @command = COALESCE(@command, '') + '  
    USE ' + database_name + '  
    insert into #temp1 (UserName,GroupName, LoginName,  
                        DefDBName, DefSchemaName,UserID,[SID])  
         Execute sp_helpuser  
    UPDATE #TEMP SET database_name=DB_NAME(),  
                     server_name=@@ServerName  
    where database_name is null  
    '  
    FROM    @databases  
    EXECUTE ( @command )  
  
    SELECT  loginname ,  
            UserName ,  
            Database_name  
    FROM    #temp  
    WHERE   LoginName = 'CORP\Spserach_dev02'   

result errors: screenshot5

In answer to your question, unfortunately there is nothing to match from this link
https://dba.stackexchange.com/questions/81595/a-query-that-lists-all-mapped-users-for-a-given-login

An example of what I need to do is shown in screenshot1.jpg

I tried to write together with colleagues, but so far I can only display the user login and the connection string

-- logins view  
  
CREATE VIEW LoginView02 AS  
select sp.name as login  
from sys.server_principals sp  
left join sys.sql_logins sl on sp.principal_id = sl.principal_id  
where sp.type not in ('G', 'R')  
GO  
-- server role view  
  
CREATE VIEW ServerRoleView02 AS  
SELECT ROL.name AS Role_Name, MEM.name AS Member_Name ,MEM.type_desc AS Member_Type ,MEM.default_schema_name AS DefaultSchema ,SP.name AS ServerLogin   
FROM sys.database_role_members AS DRM   
INNER JOIN sys.database_principals AS ROL ON DRM.role_principal_id = ROL.principal_id   
INNER JOIN sys.database_principals AS MEM ON DRM.member_principal_id = MEM.principal_id   
INNER JOIN sys.server_principals AS SP ON MEM.[sid] = SP.[sid]   
GO  
  
-- connection string view  
--drop view ConnectionStringView  
CREATE VIEW ConnectionStringView02 AS  
select 'data source=' + @@servername + ';initial catalog=' + db_name() +  
case type_desc  
when 'WINDOWS_LOGIN'   
then ';trusted_connection=true'  
else';user id=' + suser_name() + ';password=<<YourPassword>>'  
end   
as ConnectionString  
from sys.server_principals  
GO  
  
-- mapped dbs  
  
  
-- RESULT  
-- login user \ connnection string(password)\server role\ èìÿ áàçû äàííûõ\ usermaping db\  
  
SELECT lv.login 'login user', csv.ConnectionString as 'connection string', srv.ServerLogin as 'server role name', DB_NAME() AS 'current db name'  
FROM LoginView lv, ConnectionStringView csv, ServerRoleView srv  
group by lv.login, csv.ConnectionString, srv.ServerLogin  
GO  

result in screenshot6

Answer 3

The first query returns an empty result set, because presumably you do not have have a login by the name your login name. Change to an existing name to get some output. When you are pointed to existing solutions like this, don't expect them to fit your needs exactly, but you need to study them and get some understanding of what they are doing.

You complained about the second solution that you need to enter the account name, but as far as I can see that is only to filter the final SELECT, so you could simply remove that condition. However, when I looked closer on this solution, I get the impression that it has not been tested. The temp table is sometimes called #temp1 and sometimes #temp, and there is an UPDATE which seems wrong to me, so I gave up on that solution.

Anyway, here is something you can work from. In the variable @Query there is a SELECT to extract the required information for a single database. Then there is a loop to run that query in every database. Presumably you should capture that output with INSERT-EXEC, but I leave it to you to perform the final finishing to fit your needs.

   DECLARE @query nvarchar(MAX) =   
      'SELECT db_name() AS DB_name, s.name AS Login_name, u.name AS DB_User_name,  
             (SELECT r.name + '' ''  
             FROM   sys.database_principals r  
             JOIN   sys.database_role_members rm ON r.principal_id = rm.role_principal_id  
             WHERE  rm.member_principal_id = u.principal_id  
             ORDER  BY r.name  
             FOR XML PATH('''')) AS roles  
      FROM   sys.database_principals u  
      JOIN   sys.server_principals s ON u.sid = s.sid'  
     
   DECLARE @cur CURSOR,  
           @db  sysname,  
           @sp_executesql nvarchar(300)  
     
   SET @cur = CURSOR STATIC FOR  
      SELECT name   
      FROM   sys.databases   
      WHERE  state_desc = 'ONLINE'   
      ORDER BY name  
     
   OPEN @cur  
     
   WHILE 1 = 1  
   BEGIN  
      FETCH @cur INTO @db  
      IF @@fetch_status <> 0  
         BREAK  
     
      SELECT @sp_executesql = quotename(@db) + '.sys.sp_executesql'  
     
      EXEC @sp_executesql @query  
   END  

Answer 4

See:

https://www.mssqltips.com/sqlservertip/6828/sql-server-login-user-permissions-fn-my-permissions/

Answer 5

Good afternoon.
I am unable to complete the request, please help.
You need to get the following sign for users at the exit: