Using Active Directory from the CLR.zip:
=========================================
http://www.sqlservercentral.com/Forums/Topic452981-386-1.aspx
donderdag 4 december 2008
Util_ConnectionSummary -- For SQL Server 2005
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.Util_ConnectionSummary') IS NOT NULL DROP PROCEDURE Util_ConnectionSummary
GO
/**
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Util_ConnectionSummary
By Jesse Roberge - YeshuaAgapao@Yahoo.com
Reports summaries of connections, running requests, open transactions, open cursors, and blocking at 3 different levels of aggregation detail, ranking trouble groups first.
Most useful for finding SPIDs thare being hoggy right now - activity monitor gives session-scoped resource consumption, this aggregates active request scoped resource consumption.
Also useful for quickly finding blocking offenders and finding programs that are not closing cursors or transactions.
Returns 3 result sets:
Server-wide Total / Summary (No Group By)
Connections and requests grouped by LoginName, HostName, Programname
Connections and requests grouped by SessionID
Orders by ActiveReqCount DESC, OpenTranCount DESC, BlockingRequestCount DESC, BlockedReqCount DESC, ConnectionCount DESC, {group by column(s)}
Required Input Parameters
none
Optional Input Parameters
none
Usage:
EXECUTE Util_ConnectionSummary
Copyright:
Licensed under the L-GPL - a weak copyleft license - you are permitted to use this as a component of a proprietary database and call this from proprietary software.
Copyleft lets you do anything you want except plagarize, conceal the source, or prohibit copying & re-distribution of this script/proc.
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU Lesser General Public License as
published by the Free Software Foundation, either version 3 of the
License, or (at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU Lesser General Public License for more details.
see for the license text.
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
**/
CREATE PROCEDURE dbo.Util_ConnectionSummary AS
--All connections
SELECT
SUM(ConnectionCount) AS ConnectionCount,
SUM(CONVERT(bigint, ISNULL(dm_tran_session_transactions.TransactionCount,0))) AS OpenTranCount,
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.OpenCursorCount,0))) AS OpenCursorCount,
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.ClosedCursorCount,0))) AS ClosedCursorCount,
ISNULL(SUM(dm_exec_blockrequests.BlockingRequestCount),0) AS BlockingRequestCount,
SUM(dm_exec_requests.ActiveReqCount) AS ActiveReqCount,
SUM(dm_exec_requests.open_resultset_count) AS OpenResultSetCount,
SUM(dm_exec_requests.open_transaction_count) AS ActiveReqOpenTranCount,
SUM(dm_exec_requests.BlockedReqCount) AS BlockedReqCount,
SUM(dm_exec_requests.wait_time) AS WaitTime,
SUM(dm_exec_requests.cpu_time) AS CPUTime,
SUM(dm_exec_requests.total_elapsed_time) AS ElapsedTime,
SUM(dm_exec_requests.reads) AS Reads,
SUM(dm_exec_requests.writes) AS Writes,
SUM(dm_exec_requests.logical_reads) AS LogicalReads,
SUM(dm_exec_requests.row_count) AS [RowCount],
SUM(dm_exec_requests.granted_query_memory) AS GrantedQueryMemoryKB
FROM
sys.dm_exec_sessions
LEFT OUTER JOIN (
SELECT session_id, COUNT(*) AS ConnectionCount FROM sys.dm_exec_connections GROUP BY session_id
) AS dm_exec_connections ON sys.dm_exec_sessions.session_id=dm_exec_connections.session_id
LEFT OUTER JOIN (
SELECT session_id, COUNT(*) AS TransactionCount FROM sys.dm_tran_session_transactions GROUP BY session_id
) AS dm_tran_session_transactions ON sys.dm_exec_sessions.session_id=dm_tran_session_transactions.session_id
LEFT OUTER JOIN (
SELECT blocking_session_id, COUNT(*) AS BlockingRequestCount FROM sys.dm_exec_requests GROUP BY blocking_session_id
) AS dm_exec_blockrequests ON sys.dm_exec_sessions.session_id=dm_exec_blockrequests.blocking_session_id
LEFT OUTER JOIN (
SELECT session_id, SUM(CASE WHEN is_open=1 THEN 1 ELSE 0 END) AS OpenCursorCount, SUM(CASE WHEN is_open=0 THEN 1 ELSE 0 END) AS ClosedCursorCount
FROM sys.dm_exec_cursors (0)
GROUP BY session_id
) AS dm_exec_cursors ON sys.dm_exec_sessions.session_id=dm_exec_cursors.session_id
LEFT OUTER JOIN (
SELECT
session_id,
SUM(CONVERT(bigint, open_transaction_count)) AS open_transaction_count,
SUM(CONVERT(bigint, open_resultset_count)) AS open_resultset_count,
SUM(CASE WHEN total_elapsed_time IS NULL THEN 0 ELSE 1 END) AS ActiveReqCount,
SUM(CASE WHEN blocking_session_id <> 0 THEN 1 ELSE 0 END) AS BlockedReqCount,
SUM(CONVERT(bigint, wait_time)) AS wait_time,
SUM(CONVERT(bigint, cpu_time)) AS cpu_time,
SUM(CONVERT(bigint, total_elapsed_time)) AS total_elapsed_time,
SUM(CONVERT(bigint, reads)) AS Reads,
SUM(CONVERT(bigint, writes)) AS Writes,
SUM(CONVERT(bigint, logical_reads)) AS logical_reads,
SUM(CONVERT(bigint, row_count)) AS row_count,
SUM(CONVERT(bigint, granted_query_memory*8)) AS granted_query_memory
FROM sys.dm_exec_requests
GROUP BY session_id
) AS dm_exec_requests ON sys.dm_exec_sessions.session_id=dm_exec_requests.session_id
WHERE sys.dm_exec_sessions.is_user_process=1
--Connections by LoginName, Hostname, and ProgramName
SELECT
sys.dm_exec_sessions.login_name, sys.dm_exec_sessions.host_name, sys.dm_exec_sessions.program_name,
SUM(ConnectionCount) AS ConnectionCount,
SUM(CONVERT(bigint, ISNULL(dm_tran_session_transactions.TransactionCount,0))) AS OpenTranCount,
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.OpenCursorCount,0))) AS OpenCursorCount,
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.ClosedCursorCount,0))) AS ClosedCursorCount,
ISNULL(SUM(dm_exec_blockrequests.BlockingRequestCount),0) AS BlockingRequestCount,
SUM(dm_exec_requests.ActiveReqCount) AS ActiveReqCount,
SUM(dm_exec_requests.open_resultset_count) AS OpenResultSetCount,
SUM(dm_exec_requests.open_transaction_count) AS ActiveReqOpenTranCount,
SUM(dm_exec_requests.BlockedReqCount) AS BlockedReqCount,
SUM(dm_exec_requests.wait_time) AS WaitTime,
SUM(dm_exec_requests.cpu_time) AS CPUTime,
SUM(dm_exec_requests.total_elapsed_time) AS ElapsedTime,
SUM(dm_exec_requests.reads) AS Reads,
SUM(dm_exec_requests.writes) AS Writes,
SUM(dm_exec_requests.logical_reads) AS LogicalReads,
SUM(dm_exec_requests.row_count) AS [RowCount],
SUM(dm_exec_requests.granted_query_memory) AS GrantedQueryMemoryKB
FROM
sys.dm_exec_sessions
LEFT OUTER JOIN (
SELECT session_id, COUNT(*) AS ConnectionCount FROM sys.dm_exec_connections GROUP BY session_id
) AS dm_exec_connections ON sys.dm_exec_sessions.session_id=dm_exec_connections.session_id
LEFT OUTER JOIN (
SELECT session_id, COUNT(*) AS TransactionCount FROM sys.dm_tran_session_transactions GROUP BY session_id
) AS dm_tran_session_transactions ON sys.dm_exec_sessions.session_id=dm_tran_session_transactions.session_id
LEFT OUTER JOIN (
SELECT blocking_session_id, COUNT(*) AS BlockingRequestCount FROM sys.dm_exec_requests GROUP BY blocking_session_id
) AS dm_exec_blockrequests ON sys.dm_exec_sessions.session_id=dm_exec_blockrequests.blocking_session_id
LEFT OUTER JOIN (
SELECT session_id, SUM(CASE WHEN is_open=1 THEN 1 ELSE 0 END) AS OpenCursorCount, SUM(CASE WHEN is_open=0 THEN 1 ELSE 0 END) AS ClosedCursorCount
FROM sys.dm_exec_cursors (0)
GROUP BY session_id
) AS dm_exec_cursors ON sys.dm_exec_sessions.session_id=dm_exec_cursors.session_id
LEFT OUTER JOIN (
SELECT
session_id,
SUM(CONVERT(bigint, open_transaction_count)) AS open_transaction_count,
SUM(CONVERT(bigint, open_resultset_count)) AS open_resultset_count,
SUM(CASE WHEN total_elapsed_time IS NULL THEN 0 ELSE 1 END) AS ActiveReqCount,
SUM(CASE WHEN blocking_session_id <> 0 THEN 1 ELSE 0 END) AS BlockedReqCount,
SUM(CONVERT(bigint, wait_time)) AS wait_time,
SUM(CONVERT(bigint, cpu_time)) AS cpu_time,
SUM(CONVERT(bigint, total_elapsed_time)) AS total_elapsed_time,
SUM(CONVERT(bigint, reads)) AS Reads,
SUM(CONVERT(bigint, writes)) AS Writes,
SUM(CONVERT(bigint, logical_reads)) AS logical_reads,
SUM(CONVERT(bigint, row_count)) AS row_count,
SUM(CONVERT(bigint, granted_query_memory*8)) AS granted_query_memory
FROM sys.dm_exec_requests
GROUP BY session_id
) AS dm_exec_requests ON sys.dm_exec_sessions.session_id=dm_exec_requests.session_id
WHERE sys.dm_exec_sessions.is_user_process=1
GROUP BY sys.dm_exec_sessions.login_name, sys.dm_exec_sessions.host_name, sys.dm_exec_sessions.program_name
ORDER BY
ActiveReqCount DESC, OpenTranCount DESC, BlockingRequestCount DESC, BlockedReqCount DESC, ConnectionCount DESC,
sys.dm_exec_sessions.login_name, sys.dm_exec_sessions.host_name, sys.dm_exec_sessions.program_name
--Connections by session_id
SELECT
sys.dm_exec_sessions.session_id,
MAX(sys.dm_exec_sessions.login_name) AS login_name, MAX(sys.dm_exec_sessions.host_name) AS host_name,
MAX(sys.dm_exec_sessions.program_name) AS program_name, MAX(sys.dm_exec_sessions.client_interface_name) AS client_interface_name,
MAX(sys.dm_exec_sessions.status) AS status,
SUM(ConnectionCount) AS ConnectionCount,
SUM(CONVERT(bigint, ISNULL(dm_tran_session_transactions.TransactionCount,0))) AS OpenTranCount,
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.OpenCursorCount,0))) AS OpenCursorCount,
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.ClosedCursorCount,0))) AS ClosedCursorCount,
ISNULL(SUM(dm_exec_blockrequests.BlockingRequestCount),0) AS BlockingRequestCount,
SUM(dm_exec_requests.ActiveReqCount) AS ActiveReqCount,
SUM(dm_exec_requests.open_resultset_count) AS OpenResultSetCount,
SUM(dm_exec_requests.open_transaction_count) AS ActiveReqOpenTranCount,
SUM(dm_exec_requests.BlockedReqCount) AS BlockedReqCount,
SUM(dm_exec_requests.wait_time) AS WaitTime,
SUM(dm_exec_requests.cpu_time) AS CPUTime,
SUM(dm_exec_requests.total_elapsed_time) AS ElapsedTime,
SUM(dm_exec_requests.reads) AS Reads,
SUM(dm_exec_requests.writes) AS Writes,
SUM(dm_exec_requests.logical_reads) AS LogicalReads,
SUM(dm_exec_requests.row_count) AS [RowCount],
SUM(dm_exec_requests.granted_query_memory) AS GrantedQueryMemoryKB
FROM
sys.dm_exec_sessions
LEFT OUTER JOIN (
SELECT session_id, COUNT(*) AS ConnectionCount FROM sys.dm_exec_connections GROUP BY session_id
) AS dm_exec_connections ON sys.dm_exec_sessions.session_id=dm_exec_connections.session_id
LEFT OUTER JOIN (
SELECT session_id, COUNT(*) AS TransactionCount FROM sys.dm_tran_session_transactions GROUP BY session_id
) AS dm_tran_session_transactions ON sys.dm_exec_sessions.session_id=dm_tran_session_transactions.session_id
LEFT OUTER JOIN (
SELECT blocking_session_id, COUNT(*) AS BlockingRequestCount FROM sys.dm_exec_requests GROUP BY blocking_session_id
) AS dm_exec_blockrequests ON sys.dm_exec_sessions.session_id=dm_exec_blockrequests.blocking_session_id
LEFT OUTER JOIN (
SELECT session_id, SUM(CASE WHEN is_open=1 THEN 1 ELSE 0 END) AS OpenCursorCount, SUM(CASE WHEN is_open=0 THEN 1 ELSE 0 END) AS ClosedCursorCount
FROM sys.dm_exec_cursors (0)
GROUP BY session_id
) AS dm_exec_cursors ON sys.dm_exec_sessions.session_id=dm_exec_cursors.session_id
LEFT OUTER JOIN (
SELECT
session_id,
SUM(CONVERT(bigint, open_transaction_count)) AS open_transaction_count,
SUM(CONVERT(bigint, open_resultset_count)) AS open_resultset_count,
SUM(CASE WHEN total_elapsed_time IS NULL THEN 0 ELSE 1 END) AS ActiveReqCount,
SUM(CASE WHEN blocking_session_id <> 0 THEN 1 ELSE 0 END) AS BlockedReqCount,
SUM(CONVERT(bigint, wait_time)) AS wait_time,
SUM(CONVERT(bigint, cpu_time)) AS cpu_time,
SUM(CONVERT(bigint, total_elapsed_time)) AS total_elapsed_time,
SUM(CONVERT(bigint, reads)) AS Reads,
SUM(CONVERT(bigint, writes)) AS Writes,
SUM(CONVERT(bigint, logical_reads)) AS logical_reads,
SUM(CONVERT(bigint, row_count)) AS row_count,
SUM(CONVERT(bigint, granted_query_memory*8)) AS granted_query_memory
FROM sys.dm_exec_requests
GROUP BY session_id
) AS dm_exec_requests ON sys.dm_exec_sessions.session_id=dm_exec_requests.session_id
WHERE sys.dm_exec_sessions.is_user_process=1
GROUP BY sys.dm_exec_sessions.session_id
ORDER BY
ActiveReqCount DESC, OpenTranCount DESC, BlockingRequestCount DESC, BlockedReqCount DESC, ConnectionCount DESC,
login_name, program_name, host_name, session_id
GO
--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.Util_ConnectionSummary') IS NOT NULL DROP PROCEDURE Util_ConnectionSummary
GO
/**
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Util_ConnectionSummary
By Jesse Roberge - YeshuaAgapao@Yahoo.com
Reports summaries of connections, running requests, open transactions, open cursors, and blocking at 3 different levels of aggregation detail, ranking trouble groups first.
Most useful for finding SPIDs thare being hoggy right now - activity monitor gives session-scoped resource consumption, this aggregates active request scoped resource consumption.
Also useful for quickly finding blocking offenders and finding programs that are not closing cursors or transactions.
Returns 3 result sets:
Server-wide Total / Summary (No Group By)
Connections and requests grouped by LoginName, HostName, Programname
Connections and requests grouped by SessionID
Orders by ActiveReqCount DESC, OpenTranCount DESC, BlockingRequestCount DESC, BlockedReqCount DESC, ConnectionCount DESC, {group by column(s)}
Required Input Parameters
none
Optional Input Parameters
none
Usage:
EXECUTE Util_ConnectionSummary
Copyright:
Licensed under the L-GPL - a weak copyleft license - you are permitted to use this as a component of a proprietary database and call this from proprietary software.
Copyleft lets you do anything you want except plagarize, conceal the source, or prohibit copying & re-distribution of this script/proc.
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU Lesser General Public License as
published by the Free Software Foundation, either version 3 of the
License, or (at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU Lesser General Public License for more details.
see
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
**/
CREATE PROCEDURE dbo.Util_ConnectionSummary AS
--All connections
SELECT
SUM(ConnectionCount) AS ConnectionCount,
SUM(CONVERT(bigint, ISNULL(dm_tran_session_transactions.TransactionCount,0))) AS OpenTranCount,
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.OpenCursorCount,0))) AS OpenCursorCount,
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.ClosedCursorCount,0))) AS ClosedCursorCount,
ISNULL(SUM(dm_exec_blockrequests.BlockingRequestCount),0) AS BlockingRequestCount,
SUM(dm_exec_requests.ActiveReqCount) AS ActiveReqCount,
SUM(dm_exec_requests.open_resultset_count) AS OpenResultSetCount,
SUM(dm_exec_requests.open_transaction_count) AS ActiveReqOpenTranCount,
SUM(dm_exec_requests.BlockedReqCount) AS BlockedReqCount,
SUM(dm_exec_requests.wait_time) AS WaitTime,
SUM(dm_exec_requests.cpu_time) AS CPUTime,
SUM(dm_exec_requests.total_elapsed_time) AS ElapsedTime,
SUM(dm_exec_requests.reads) AS Reads,
SUM(dm_exec_requests.writes) AS Writes,
SUM(dm_exec_requests.logical_reads) AS LogicalReads,
SUM(dm_exec_requests.row_count) AS [RowCount],
SUM(dm_exec_requests.granted_query_memory) AS GrantedQueryMemoryKB
FROM
sys.dm_exec_sessions
LEFT OUTER JOIN (
SELECT session_id, COUNT(*) AS ConnectionCount FROM sys.dm_exec_connections GROUP BY session_id
) AS dm_exec_connections ON sys.dm_exec_sessions.session_id=dm_exec_connections.session_id
LEFT OUTER JOIN (
SELECT session_id, COUNT(*) AS TransactionCount FROM sys.dm_tran_session_transactions GROUP BY session_id
) AS dm_tran_session_transactions ON sys.dm_exec_sessions.session_id=dm_tran_session_transactions.session_id
LEFT OUTER JOIN (
SELECT blocking_session_id, COUNT(*) AS BlockingRequestCount FROM sys.dm_exec_requests GROUP BY blocking_session_id
) AS dm_exec_blockrequests ON sys.dm_exec_sessions.session_id=dm_exec_blockrequests.blocking_session_id
LEFT OUTER JOIN (
SELECT session_id, SUM(CASE WHEN is_open=1 THEN 1 ELSE 0 END) AS OpenCursorCount, SUM(CASE WHEN is_open=0 THEN 1 ELSE 0 END) AS ClosedCursorCount
FROM sys.dm_exec_cursors (0)
GROUP BY session_id
) AS dm_exec_cursors ON sys.dm_exec_sessions.session_id=dm_exec_cursors.session_id
LEFT OUTER JOIN (
SELECT
session_id,
SUM(CONVERT(bigint, open_transaction_count)) AS open_transaction_count,
SUM(CONVERT(bigint, open_resultset_count)) AS open_resultset_count,
SUM(CASE WHEN total_elapsed_time IS NULL THEN 0 ELSE 1 END) AS ActiveReqCount,
SUM(CASE WHEN blocking_session_id <> 0 THEN 1 ELSE 0 END) AS BlockedReqCount,
SUM(CONVERT(bigint, wait_time)) AS wait_time,
SUM(CONVERT(bigint, cpu_time)) AS cpu_time,
SUM(CONVERT(bigint, total_elapsed_time)) AS total_elapsed_time,
SUM(CONVERT(bigint, reads)) AS Reads,
SUM(CONVERT(bigint, writes)) AS Writes,
SUM(CONVERT(bigint, logical_reads)) AS logical_reads,
SUM(CONVERT(bigint, row_count)) AS row_count,
SUM(CONVERT(bigint, granted_query_memory*8)) AS granted_query_memory
FROM sys.dm_exec_requests
GROUP BY session_id
) AS dm_exec_requests ON sys.dm_exec_sessions.session_id=dm_exec_requests.session_id
WHERE sys.dm_exec_sessions.is_user_process=1
--Connections by LoginName, Hostname, and ProgramName
SELECT
sys.dm_exec_sessions.login_name, sys.dm_exec_sessions.host_name, sys.dm_exec_sessions.program_name,
SUM(ConnectionCount) AS ConnectionCount,
SUM(CONVERT(bigint, ISNULL(dm_tran_session_transactions.TransactionCount,0))) AS OpenTranCount,
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.OpenCursorCount,0))) AS OpenCursorCount,
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.ClosedCursorCount,0))) AS ClosedCursorCount,
ISNULL(SUM(dm_exec_blockrequests.BlockingRequestCount),0) AS BlockingRequestCount,
SUM(dm_exec_requests.ActiveReqCount) AS ActiveReqCount,
SUM(dm_exec_requests.open_resultset_count) AS OpenResultSetCount,
SUM(dm_exec_requests.open_transaction_count) AS ActiveReqOpenTranCount,
SUM(dm_exec_requests.BlockedReqCount) AS BlockedReqCount,
SUM(dm_exec_requests.wait_time) AS WaitTime,
SUM(dm_exec_requests.cpu_time) AS CPUTime,
SUM(dm_exec_requests.total_elapsed_time) AS ElapsedTime,
SUM(dm_exec_requests.reads) AS Reads,
SUM(dm_exec_requests.writes) AS Writes,
SUM(dm_exec_requests.logical_reads) AS LogicalReads,
SUM(dm_exec_requests.row_count) AS [RowCount],
SUM(dm_exec_requests.granted_query_memory) AS GrantedQueryMemoryKB
FROM
sys.dm_exec_sessions
LEFT OUTER JOIN (
SELECT session_id, COUNT(*) AS ConnectionCount FROM sys.dm_exec_connections GROUP BY session_id
) AS dm_exec_connections ON sys.dm_exec_sessions.session_id=dm_exec_connections.session_id
LEFT OUTER JOIN (
SELECT session_id, COUNT(*) AS TransactionCount FROM sys.dm_tran_session_transactions GROUP BY session_id
) AS dm_tran_session_transactions ON sys.dm_exec_sessions.session_id=dm_tran_session_transactions.session_id
LEFT OUTER JOIN (
SELECT blocking_session_id, COUNT(*) AS BlockingRequestCount FROM sys.dm_exec_requests GROUP BY blocking_session_id
) AS dm_exec_blockrequests ON sys.dm_exec_sessions.session_id=dm_exec_blockrequests.blocking_session_id
LEFT OUTER JOIN (
SELECT session_id, SUM(CASE WHEN is_open=1 THEN 1 ELSE 0 END) AS OpenCursorCount, SUM(CASE WHEN is_open=0 THEN 1 ELSE 0 END) AS ClosedCursorCount
FROM sys.dm_exec_cursors (0)
GROUP BY session_id
) AS dm_exec_cursors ON sys.dm_exec_sessions.session_id=dm_exec_cursors.session_id
LEFT OUTER JOIN (
SELECT
session_id,
SUM(CONVERT(bigint, open_transaction_count)) AS open_transaction_count,
SUM(CONVERT(bigint, open_resultset_count)) AS open_resultset_count,
SUM(CASE WHEN total_elapsed_time IS NULL THEN 0 ELSE 1 END) AS ActiveReqCount,
SUM(CASE WHEN blocking_session_id <> 0 THEN 1 ELSE 0 END) AS BlockedReqCount,
SUM(CONVERT(bigint, wait_time)) AS wait_time,
SUM(CONVERT(bigint, cpu_time)) AS cpu_time,
SUM(CONVERT(bigint, total_elapsed_time)) AS total_elapsed_time,
SUM(CONVERT(bigint, reads)) AS Reads,
SUM(CONVERT(bigint, writes)) AS Writes,
SUM(CONVERT(bigint, logical_reads)) AS logical_reads,
SUM(CONVERT(bigint, row_count)) AS row_count,
SUM(CONVERT(bigint, granted_query_memory*8)) AS granted_query_memory
FROM sys.dm_exec_requests
GROUP BY session_id
) AS dm_exec_requests ON sys.dm_exec_sessions.session_id=dm_exec_requests.session_id
WHERE sys.dm_exec_sessions.is_user_process=1
GROUP BY sys.dm_exec_sessions.login_name, sys.dm_exec_sessions.host_name, sys.dm_exec_sessions.program_name
ORDER BY
ActiveReqCount DESC, OpenTranCount DESC, BlockingRequestCount DESC, BlockedReqCount DESC, ConnectionCount DESC,
sys.dm_exec_sessions.login_name, sys.dm_exec_sessions.host_name, sys.dm_exec_sessions.program_name
--Connections by session_id
SELECT
sys.dm_exec_sessions.session_id,
MAX(sys.dm_exec_sessions.login_name) AS login_name, MAX(sys.dm_exec_sessions.host_name) AS host_name,
MAX(sys.dm_exec_sessions.program_name) AS program_name, MAX(sys.dm_exec_sessions.client_interface_name) AS client_interface_name,
MAX(sys.dm_exec_sessions.status) AS status,
SUM(ConnectionCount) AS ConnectionCount,
SUM(CONVERT(bigint, ISNULL(dm_tran_session_transactions.TransactionCount,0))) AS OpenTranCount,
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.OpenCursorCount,0))) AS OpenCursorCount,
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.ClosedCursorCount,0))) AS ClosedCursorCount,
ISNULL(SUM(dm_exec_blockrequests.BlockingRequestCount),0) AS BlockingRequestCount,
SUM(dm_exec_requests.ActiveReqCount) AS ActiveReqCount,
SUM(dm_exec_requests.open_resultset_count) AS OpenResultSetCount,
SUM(dm_exec_requests.open_transaction_count) AS ActiveReqOpenTranCount,
SUM(dm_exec_requests.BlockedReqCount) AS BlockedReqCount,
SUM(dm_exec_requests.wait_time) AS WaitTime,
SUM(dm_exec_requests.cpu_time) AS CPUTime,
SUM(dm_exec_requests.total_elapsed_time) AS ElapsedTime,
SUM(dm_exec_requests.reads) AS Reads,
SUM(dm_exec_requests.writes) AS Writes,
SUM(dm_exec_requests.logical_reads) AS LogicalReads,
SUM(dm_exec_requests.row_count) AS [RowCount],
SUM(dm_exec_requests.granted_query_memory) AS GrantedQueryMemoryKB
FROM
sys.dm_exec_sessions
LEFT OUTER JOIN (
SELECT session_id, COUNT(*) AS ConnectionCount FROM sys.dm_exec_connections GROUP BY session_id
) AS dm_exec_connections ON sys.dm_exec_sessions.session_id=dm_exec_connections.session_id
LEFT OUTER JOIN (
SELECT session_id, COUNT(*) AS TransactionCount FROM sys.dm_tran_session_transactions GROUP BY session_id
) AS dm_tran_session_transactions ON sys.dm_exec_sessions.session_id=dm_tran_session_transactions.session_id
LEFT OUTER JOIN (
SELECT blocking_session_id, COUNT(*) AS BlockingRequestCount FROM sys.dm_exec_requests GROUP BY blocking_session_id
) AS dm_exec_blockrequests ON sys.dm_exec_sessions.session_id=dm_exec_blockrequests.blocking_session_id
LEFT OUTER JOIN (
SELECT session_id, SUM(CASE WHEN is_open=1 THEN 1 ELSE 0 END) AS OpenCursorCount, SUM(CASE WHEN is_open=0 THEN 1 ELSE 0 END) AS ClosedCursorCount
FROM sys.dm_exec_cursors (0)
GROUP BY session_id
) AS dm_exec_cursors ON sys.dm_exec_sessions.session_id=dm_exec_cursors.session_id
LEFT OUTER JOIN (
SELECT
session_id,
SUM(CONVERT(bigint, open_transaction_count)) AS open_transaction_count,
SUM(CONVERT(bigint, open_resultset_count)) AS open_resultset_count,
SUM(CASE WHEN total_elapsed_time IS NULL THEN 0 ELSE 1 END) AS ActiveReqCount,
SUM(CASE WHEN blocking_session_id <> 0 THEN 1 ELSE 0 END) AS BlockedReqCount,
SUM(CONVERT(bigint, wait_time)) AS wait_time,
SUM(CONVERT(bigint, cpu_time)) AS cpu_time,
SUM(CONVERT(bigint, total_elapsed_time)) AS total_elapsed_time,
SUM(CONVERT(bigint, reads)) AS Reads,
SUM(CONVERT(bigint, writes)) AS Writes,
SUM(CONVERT(bigint, logical_reads)) AS logical_reads,
SUM(CONVERT(bigint, row_count)) AS row_count,
SUM(CONVERT(bigint, granted_query_memory*8)) AS granted_query_memory
FROM sys.dm_exec_requests
GROUP BY session_id
) AS dm_exec_requests ON sys.dm_exec_sessions.session_id=dm_exec_requests.session_id
WHERE sys.dm_exec_sessions.is_user_process=1
GROUP BY sys.dm_exec_sessions.session_id
ORDER BY
ActiveReqCount DESC, OpenTranCount DESC, BlockingRequestCount DESC, BlockedReqCount DESC, ConnectionCount DESC,
login_name, program_name, host_name, session_id
GO
--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
sp_who2 to sp_whoDB
http://sqlserverinternals.blogspot.com/search?updated-min=2006-01-01T00%3A00%3A00-08%3A00&updated-max=2007-01-01T00%3A00%3A00-08%3A00&max-results=21
As a DBA, there might not be a date that you didn’t use this tool. It is a wonderful tool and you will also be asked on attending interviews. But, have you wondered of adding parameters to this procedure and use it only to show only information on specific database? Well, a small modification of this stored procedure helped me in doing this, I am sure it will help you too. There isn’t much modification to it but the idea of only seeing the connection to database that you want is in my opinion is good one. You can create this sp in master database and use the same as you use sp_who2. The only difference is you can pass the database name as a parameter.
--------------------------------:
CREATE PROCEDURE sp_whoDB
@dbname sysname = null,
@loginame sysname = NULL
as
set nocount on
declare
@retcode int
declare @dbid int
select @dbid = dbid from sysdatabases where name = @dbname
declare
@sidlow varbinary(85)
,@sidhigh varbinary(85)
,@sid1 varbinary(85)
,@spidlow int
,@spidhigh int
declare
@charMaxLenLoginName varchar(6)
,@charMaxLenDBName varchar(6)
,@charMaxLenCPUTime varchar(10)
,@charMaxLenDiskIO varchar(10)
,@charMaxLenHostName varchar(10)
,@charMaxLenProgramName varchar(10)
,@charMaxLenLastBatch varchar(10)
,@charMaxLenCommand varchar(10)
declare
@charsidlow varchar(85)
,@charsidhigh varchar(85)
,@charspidlow varchar(11)
,@charspidhigh varchar(11)
--------
select
@retcode = 0 -- 0=good ,1=bad.
--------defaults
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))
select
@spidlow = 0
,@spidhigh = 32767
--------------------------------------------------------------
IF (@loginame IS NULL) --Simple default to all LoginNames.
GOTO LABEL_17PARM1EDITED
--------
-- select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from master.dbo.syslogins where loginname = @loginame)
select @sid1 = sid from master.dbo.syslogins where loginname = @loginame
IF (@sid1 IS NOT NULL) --Parm is a recognized login name.
begin
select @sidlow = suser_sid(@loginame)
,@sidhigh = suser_sid(@loginame)
GOTO LABEL_17PARM1EDITED
end
--------
IF (lower(@loginame) IN ('active')) --Special action, not sleeping.
begin
select @loginame = lower(@loginame)
GOTO LABEL_17PARM1EDITED
end
--------
IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) --Is a number.
begin
select
@spidlow = convert(int, @loginame)
,@spidhigh = convert(int, @loginame)
GOTO LABEL_17PARM1EDITED
end
--------
RaisError(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN
LABEL_17PARM1EDITED:
-------------------- Capture consistent sysprocesses. -------------------
SELECT
spid
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame))
as loginname
,spid as 'spid_sort'
, substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,last_batch,113) ,13 ,8 )
as 'last_batch_char'
INTO #tb1_sysprocesses
from master.dbo.sysprocesses (nolock)
where (dbid = @dbId or @dbName is null) and spid > 12
--------Screen out any rows?
IF (@loginame IN ('active'))
DELETE #tb1_sysprocesses
where lower(status) = 'sleeping'
and upper(cmd) IN (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
)
and blocked = 0
--------Prepare to dynamically optimize column widths.
Select
@charsidlow = convert(varchar(85),@sidlow)
,@charsidhigh = convert(varchar(85),@sidhigh)
,@charspidlow = convert(varchar,@spidlow)
,@charspidhigh = convert(varchar,@spidhigh)
SELECT
@charMaxLenLoginName =
convert( varchar
,isnull( max( datalength(loginname)) ,5)
)
,@charMaxLenDBName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)
)
,@charMaxLenCPUTime =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)
)
,@charMaxLenDiskIO =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)
)
,@charMaxLenCommand =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)
)
,@charMaxLenHostName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)
)
,@charMaxLenProgramName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)
)
,@charMaxLenLastBatch =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9)
)
from
#tb1_sysprocesses
where
-- sid >= @sidlow
-- and sid <= @sidhigh
-- and
spid >= @spidlow
and spid <= @spidhigh
--------Output the report.
EXECUTE(
'
SET nocount off
SELECT
SPID = convert(char(5),spid)
,Status =
CASE lower(status)
When ''sleeping'' Then lower(status)
Else upper(status)
END
,Login = substring(loginname,1,' + @charMaxLenLoginName + ')
,HostName =
CASE hostname
When Null Then '' .''
When '' '' Then '' .''
Else substring(hostname,1,' + @charMaxLenHostName + ')
END
,BlkBy =
CASE isnull(convert(char(5),blocked),''0'')
When ''0'' Then '' .''
Else isnull(convert(char(5),blocked),''0'')
END
,DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
,Command = substring(cmd,1,' + @charMaxLenCommand + ')
,CPUTime = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
,DiskIO = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')
,LastBatch = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')
,ProgramName = substring(program_name,1,' + @charMaxLenProgramName + ')
,SPID = convert(char(5),spid) --Handy extra for right-scrolling users.
from
#tb1_sysprocesses --Usually DB qualification is needed in exec().
where
spid >= ' + @charspidlow + '
and spid <= ' + @charspidhigh + '
order by spid_sort
SET nocount on
'
)
/*****AKUNDONE: removed from where-clause in above EXEC sqlstr
sid >= ' + @charsidlow + '
and sid <= ' + @charsidhigh + '
and
**************/
LABEL_86RETURN:
if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses
return @retcode -- sp_who2
As a DBA, there might not be a date that you didn’t use this tool. It is a wonderful tool and you will also be asked on attending interviews. But, have you wondered of adding parameters to this procedure and use it only to show only information on specific database? Well, a small modification of this stored procedure helped me in doing this, I am sure it will help you too. There isn’t much modification to it but the idea of only seeing the connection to database that you want is in my opinion is good one. You can create this sp in master database and use the same as you use sp_who2. The only difference is you can pass the database name as a parameter.
--------------------------------:
CREATE PROCEDURE sp_whoDB
@dbname sysname = null,
@loginame sysname = NULL
as
set nocount on
declare
@retcode int
declare @dbid int
select @dbid = dbid from sysdatabases where name = @dbname
declare
@sidlow varbinary(85)
,@sidhigh varbinary(85)
,@sid1 varbinary(85)
,@spidlow int
,@spidhigh int
declare
@charMaxLenLoginName varchar(6)
,@charMaxLenDBName varchar(6)
,@charMaxLenCPUTime varchar(10)
,@charMaxLenDiskIO varchar(10)
,@charMaxLenHostName varchar(10)
,@charMaxLenProgramName varchar(10)
,@charMaxLenLastBatch varchar(10)
,@charMaxLenCommand varchar(10)
declare
@charsidlow varchar(85)
,@charsidhigh varchar(85)
,@charspidlow varchar(11)
,@charspidhigh varchar(11)
--------
select
@retcode = 0 -- 0=good ,1=bad.
--------defaults
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))
select
@spidlow = 0
,@spidhigh = 32767
--------------------------------------------------------------
IF (@loginame IS NULL) --Simple default to all LoginNames.
GOTO LABEL_17PARM1EDITED
--------
-- select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from master.dbo.syslogins where loginname = @loginame)
select @sid1 = sid from master.dbo.syslogins where loginname = @loginame
IF (@sid1 IS NOT NULL) --Parm is a recognized login name.
begin
select @sidlow = suser_sid(@loginame)
,@sidhigh = suser_sid(@loginame)
GOTO LABEL_17PARM1EDITED
end
--------
IF (lower(@loginame) IN ('active')) --Special action, not sleeping.
begin
select @loginame = lower(@loginame)
GOTO LABEL_17PARM1EDITED
end
--------
IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) --Is a number.
begin
select
@spidlow = convert(int, @loginame)
,@spidhigh = convert(int, @loginame)
GOTO LABEL_17PARM1EDITED
end
--------
RaisError(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN
LABEL_17PARM1EDITED:
-------------------- Capture consistent sysprocesses. -------------------
SELECT
spid
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame))
as loginname
,spid as 'spid_sort'
, substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,last_batch,113) ,13 ,8 )
as 'last_batch_char'
INTO #tb1_sysprocesses
from master.dbo.sysprocesses (nolock)
where (dbid = @dbId or @dbName is null) and spid > 12
--------Screen out any rows?
IF (@loginame IN ('active'))
DELETE #tb1_sysprocesses
where lower(status) = 'sleeping'
and upper(cmd) IN (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
)
and blocked = 0
--------Prepare to dynamically optimize column widths.
Select
@charsidlow = convert(varchar(85),@sidlow)
,@charsidhigh = convert(varchar(85),@sidhigh)
,@charspidlow = convert(varchar,@spidlow)
,@charspidhigh = convert(varchar,@spidhigh)
SELECT
@charMaxLenLoginName =
convert( varchar
,isnull( max( datalength(loginname)) ,5)
)
,@charMaxLenDBName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)
)
,@charMaxLenCPUTime =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)
)
,@charMaxLenDiskIO =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)
)
,@charMaxLenCommand =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)
)
,@charMaxLenHostName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)
)
,@charMaxLenProgramName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)
)
,@charMaxLenLastBatch =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9)
)
from
#tb1_sysprocesses
where
-- sid >= @sidlow
-- and sid <= @sidhigh
-- and
spid >= @spidlow
and spid <= @spidhigh
--------Output the report.
EXECUTE(
'
SET nocount off
SELECT
SPID = convert(char(5),spid)
,Status =
CASE lower(status)
When ''sleeping'' Then lower(status)
Else upper(status)
END
,Login = substring(loginname,1,' + @charMaxLenLoginName + ')
,HostName =
CASE hostname
When Null Then '' .''
When '' '' Then '' .''
Else substring(hostname,1,' + @charMaxLenHostName + ')
END
,BlkBy =
CASE isnull(convert(char(5),blocked),''0'')
When ''0'' Then '' .''
Else isnull(convert(char(5),blocked),''0'')
END
,DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
,Command = substring(cmd,1,' + @charMaxLenCommand + ')
,CPUTime = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
,DiskIO = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')
,LastBatch = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')
,ProgramName = substring(program_name,1,' + @charMaxLenProgramName + ')
,SPID = convert(char(5),spid) --Handy extra for right-scrolling users.
from
#tb1_sysprocesses --Usually DB qualification is needed in exec().
where
spid >= ' + @charspidlow + '
and spid <= ' + @charspidhigh + '
order by spid_sort
SET nocount on
'
)
/*****AKUNDONE: removed from where-clause in above EXEC sqlstr
sid >= ' + @charsidlow + '
and sid <= ' + @charsidhigh + '
and
**************/
LABEL_86RETURN:
if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses
return @retcode -- sp_who2
User name, group name and thier default database permissions
User name, group name and thier default database :
================================================
http://sqlserverinternals.blogspot.com/2006/06/user-name-group-name-and-thier-default.html
-----------------:
The following script will help you to list all user name, group name and thier default database. The script will help you to list all of the above on single instance but includes all databases. I use this script as starting point to fix any security issues.
set nocount on
declare @dbName sysname, -- database name
@dbid int -- database Id
IF (object_id('tempdb..#userDetails') IS not Null)
Drop Table #userDetails
-- create temp table to hold info
BEGIN
CREATE TABLE #userDetails
(DbName sysname,
UserName sysname,
GroupName sysname,
LoginName sysname,
UserDefaultDB sysname)
END
declare @dbnames table(dbid int not null primary key clustered, dbname nvarchar(100))
INSERT INTO @dbnames(dbid, dbname)
select dbid, name from master.dbo.sysdatabases where dbid > 4 and name not like '%Sharepoint%'
select @dbid = max(dbid) from @dbnames
while @dbid is not null
begin
SELECT @dbName = dbname FROM @dbnames
WHERE dbid = @dbid
EXECUTE(
'use ' + @dbName + '
INSERT INTO #userDetails(DbName, UserName, GroupName, LoginName, UserDefaultDB)
SELECT db_name() as DBName, usu.name As UserName , case when (usg.uid is null) then ''public'' else usg.name end as GroupName ,
lo.loginname ,lo.dbname as UserDefaultDbName
from sysusers usu
join
(sysmembers mem inner join sysusers usg on mem.groupuid = usg.uid) on usu.uid = mem.memberuid
join master.dbo.syslogins lo on usu.sid = lo.sid
where (usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess = 1)
and (usg.issqlrole = 1 or usg.uid is null)
')
select @dbid = max(dbid) from @dbnames
where dbid < @dbid end select * from #userDetails order by DbName, UserName, GroupName asc
================================================
http://sqlserverinternals.blogspot.com/2006/06/user-name-group-name-and-thier-default.html
-----------------:
The following script will help you to list all user name, group name and thier default database. The script will help you to list all of the above on single instance but includes all databases. I use this script as starting point to fix any security issues.
set nocount on
declare @dbName sysname, -- database name
@dbid int -- database Id
IF (object_id('tempdb..#userDetails') IS not Null)
Drop Table #userDetails
-- create temp table to hold info
BEGIN
CREATE TABLE #userDetails
(DbName sysname,
UserName sysname,
GroupName sysname,
LoginName sysname,
UserDefaultDB sysname)
END
declare @dbnames table(dbid int not null primary key clustered, dbname nvarchar(100))
INSERT INTO @dbnames(dbid, dbname)
select dbid, name from master.dbo.sysdatabases where dbid > 4 and name not like '%Sharepoint%'
select @dbid = max(dbid) from @dbnames
while @dbid is not null
begin
SELECT @dbName = dbname FROM @dbnames
WHERE dbid = @dbid
EXECUTE(
'use ' + @dbName + '
INSERT INTO #userDetails(DbName, UserName, GroupName, LoginName, UserDefaultDB)
SELECT db_name() as DBName, usu.name As UserName , case when (usg.uid is null) then ''public'' else usg.name end as GroupName ,
lo.loginname ,lo.dbname as UserDefaultDbName
from sysusers usu
join
(sysmembers mem inner join sysusers usg on mem.groupuid = usg.uid) on usu.uid = mem.memberuid
join master.dbo.syslogins lo on usu.sid = lo.sid
where (usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess = 1)
and (usg.issqlrole = 1 or usg.uid is null)
')
select @dbid = max(dbid) from @dbnames
where dbid < @dbid end select * from #userDetails order by DbName, UserName, GroupName asc
Abonneren op:
Posts (Atom)