2Xu58rmk

1----------------------------------------------------------------
2-- Determine whether the connections to the server are using kerberos or not
3----------------------------------------------------------------
4SELECT sys.dm_exec_connections.session_id         AS SPID, 
5       sys.dm_exec_connections.connect_time       AS Connect_Time, 
6       Db_name(dbid)                              AS DatabaseName, 
7       loginame                                   AS LoginName, 
8       sys.dm_exec_connections.auth_scheme        AS Auth_Scheme, 
9       sys.dm_exec_connections.net_transport      AS Net_Transport, 
10       sys.dm_exec_connections.protocol_type      AS Protocol_Type, 
11       sys.dm_exec_connections.client_net_address AS Client_Net_Address, 
12       sys.dm_exec_connections.local_net_address  AS Local_Net_Address, 
13       sys.dm_exec_connections.local_tcp_port     AS Local_TCP_Port 
14FROM   sys.sysprocesses 
15       RIGHT OUTER JOIN sys.dm_exec_connections 
16                     ON sys.sysprocesses.spid = 
17                        sys.dm_exec_connections.session_id 
18ORDER  BY auth_scheme, 
19          net_transport 
20
21----------------------------------------------------------------
22-- List All Logins With Databases Role Mapping
23----------------------------------------------------------------
24CREATE PROCEDURE spListAllLoginsWithDatabasesRoleMapping 
25AS 
26  BEGIN 
27      SET NOCOUNT ON; 
28
29      DECLARE @name    SYSNAME, 
30              @sql     NVARCHAR(4000), 
31              @maxlen1 SMALLINT, 
32              @maxlen2 SMALLINT, 
33              @maxlen3 SMALLINT 
34
35      IF EXISTS (SELECT table_name 
36                 FROM   tempdb.INFORMATION_SCHEMA.tables 
37                 WHERE  table_name LIKE '#tmpTable') 
38        DROP TABLE #tmpTable 
39
40      CREATE TABLE #tmpTable 
41        ( 
42           dbname   SYSNAME NOT NULL, 
43           username SYSNAME NOT NULL, 
44           rolename SYSNAME NOT NULL 
45        ) 
46
47      DECLARE c1 CURSOR FOR 
48        SELECT NAME 
49        FROM   master.sys.databases 
50        WHERE  state = 0 
51
52      OPEN c1 
53
54      FETCH c1 INTO @name 
55
56      WHILE @@FETCH_STATUS >= 0 
57        BEGIN 
58            SELECT @sql = 'INSERT INTO #tmpTable SELECT N''' + @name + ''', a.name, c.name FROM [' + @name + '].sys.database_principals a  JOIN [' 
59                          + @name + '].sys.database_role_members b ON b.member_principal_id = a.principal_id JOIN [' + @name 
60                          + '].sys.database_principals c ON c.principal_id = b.role_principal_id WHERE a.name != ''dbo''' 
61
62            EXECUTE (@sql) 
63
64            FETCH c1 INTO @name 
65        END 
66
67      CLOSE c1 
68
69      DEALLOCATE c1 
70
71      SELECT @maxlen1 = ( Max(Len(COALESCE(dbname, 'NULL'))) + 2 ) 
72      FROM   #tmpTable 
73
74      SELECT @maxlen2 = ( Max(Len(COALESCE(username, 'NULL'))) + 2 ) 
75      FROM   #tmpTable 
76
77      SELECT @maxlen3 = ( Max(Len(COALESCE(rolename, 'NULL'))) + 2 ) 
78      FROM   #tmpTable 
79
80      SET @sql = 'SELECT LEFT(DBName, ' 
81                 + Ltrim(Str(@maxlen1)) + ') AS ''DB Name'', ' 
82      SET @sql = @sql + 'LEFT(UserName, ' 
83                 + Ltrim(Str(@maxlen2)) 
84                 + ') AS ''User Name'', ' 
85      SET @sql = @sql + 'LEFT(RoleName, ' 
86                 + Ltrim(Str(@maxlen3)) 
87                 + ') AS ''Role Name'' ' 
88      SET @sql = @sql + 'FROM #tmpTable ' 
89      SET @sql = @sql + 'ORDER BY DBName, UserName' 
90
91      EXEC(@sql) 
92  END