Category Archives: 2744

SQL Server Management Studio – standard reports in the Summary Page

In SQL Server Management Studio, the Summary Page can be displayed at any time by pressing F7 (or, to be chosen from the menu –> View –> Summary). When you select an item in the Object Explorer of SQL Server Management Studio, information about that object is presented in the document window called the Summary Page.


You can opt to display the Summary Page when starting SQL Server Management Studio.


–> Tools –> Options –> Environment –> General –> At startup: Open Object Explorer


Note that the Summary Page will be displayed when the option ‘Open Object Explorer’ is being chosen. Other options will *not* bring up the Summary Page at startup (which include ‘Open new query window’, ‘Open Object Explorer and new query’, ‘Open empty environment’).


Following is the list of reports that can be found within the Summary Page when you choose a *SQL Server* in the Object Explorer. This list will be changed when you choose different kinds of database objects.


~ server dashboard
~ configuration changes history
~ schema changes history
~ scheduler health
~ memory consumption
~ activity – all blocking transactions
~ activity – all cursors
~ activity – top cursors
~ activity – all sessions
~ activity – top sessions
~ activity – dormant sessions
~ activity – top connections
~ top transactions by age
~ top transactions by blocked transactions count
~ top transactions by locks count
~ performance – batch execution statistics
~ performance – object execution statistics
~ performance – top queries by average CPU time
~ performance – top queries by average IO
~ performance – top queries by total CPU time
~ performance – top queries by total IO
~ service broker statistics
~ transaction log shipping status


Recently, Buck Woody blogged the standard reports that can be found under the Summary Page. More information can be found here.


http://blogs.msdn.com/buckwoody/archive/tags/Standard+Reports/default.aspx

List of logins with creation dates

========== SQL Server Management Studio ==========

If you have installed the *client tools* of SQL Server 2005, you can use SQL Server Management Studio to connect to your SQL Server, open the security node, and right click to apply filter settings for showing the logins. There exists a filter criterion for login creation date.

You can also view the list of logins in the summary page (View –> Summary).

–> SQL Server Management Studio
   –> Object Explorer
      –> (+) your SQL Server
         –> (+) Security
            –> (+) Logins
–> right click for filter settings
or, –> View –> Summary –> List (login names and creation dates are shown)

========== sys.server_principals ==========

Login information can also be obtained from the catalog views of SQL Server.
- sys.server_principals contains a row for every server-level principal

- master..syslogins in SQL Server 2000

- sys.syslogins in SQL Server 2005 (for backward compatibility)

SELECT
               
'Server[@Name='
                        + quotename(CAST(serverproperty(N'Servername') AS sysname),'''')
                        + ']'
                        + '/Login[@Name='
                        + quotename(log.name,'''')
                        + ']' AS [Urn]
               
, log.name AS [Name]
               
, CASE WHEN N'U' = log.type THEN 0
                        WHEN N'G' = log.type THEN 1
                        WHEN N'S' = log.type THEN 2
                        WHEN N'C' = log.type THEN 3
                        WHEN N'K' = log.type THEN 4
                        END AS [LoginType]
               
, log.is_disabled AS [IsDisabled]
               
, log.create_date AS [CreateDate]
       
FROM
               
sys.server_principals AS log
       
WHERE
               
(log.type in ('U', 'G', 'S', 'C', 'K')
                        AND log.principal_id not between 101 and 255
                        AND log.name <> N'##MS_AgentSigningCertificate##')
       
ORDER BY
               
[Name] ASC

========== For more information ==========

- sys.server_principals (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188786.aspx

- syslogins
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-l_64mr.asp