{"id":1166,"date":"2010-09-29T15:43:57","date_gmt":"2010-09-29T10:13:57","guid":{"rendered":"http:\/\/JitendraZaa.com\/blog\/?p=1166"},"modified":"2010-09-29T15:43:57","modified_gmt":"2010-09-29T10:13:57","slug":"get-login-session-count-session-mode-user-name-machine-name-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/get-login-session-count-session-mode-user-name-machine-name-in-sql-server\/","title":{"rendered":"Get Login Session count, Session Mode, User Name, Machine name in SQL Server"},"content":{"rendered":"<p>In SQL server <strong>sys.dm_exec_sessions <\/strong>Stores the login information about users like username, authentication mode (Windows or SQL), login time, machine name and therefore we can easily determine the current state of SQL server like Session count, User name, machine name etc.<\/p>\n<p><!--more--><\/p>\n<p>To achieve this below query is used:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\n SELECT 'Authentication Method'=(\n\tCASE\n\t\tWHEN nt_user_name IS not null THEN 'Windows Authentication'\n\t\tELSE 'SQL Authentication'\n\tEND),\n   login_name AS 'Login Name',\n   ISNULL(nt_user_name,'N\/A') AS 'Windows Login Name',\n   COUNT(session_id) AS 'Session Count',\n   host_name As 'Host'\n   FROM sys.dm_exec_sessions\n   GROUP BY login_name,nt_user_name,host_name\n<\/pre>\n<p>In output you will get the detail of users login with there machine name and session<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Get Login Session count, Session Mode, User Name, Machine name in SQL Server<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"jz_research_post":"","_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[14],"tags":[292],"class_list":["post-1166","post","type-post","status-publish","format-standard","hentry","category-sqlserver","tag-sql-server"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":1105,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/install-sql-server-2005-in-windows-xp\/","url_meta":{"origin":1166,"position":0},"title":"Install SQL Server 2005 in Windows XP","author":"Jitendra","date":"September 16, 2010","format":false,"excerpt":"Steps to Install SQL Server 2005 - Standard edition in Windows XP","rel":"","context":"In &quot;SQL Server&quot;","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"SQL Server 2005 Accept License","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/09\/SQL-Server-2005-Accept-License-300x246.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1121,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/create-new-user-in-sql-server-2005\/","url_meta":{"origin":1166,"position":1},"title":"Create new User in SQL Server 2005","author":"Jitendra","date":"September 18, 2010","format":false,"excerpt":"Create new User in SQL Server 2005","rel":"","context":"In &quot;SQL Server&quot;","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"SQL Server Security folder","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/09\/SQL-Server-Security-folder.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1125,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/get-table-information-column-name-data-type-in-sql-server\/","url_meta":{"origin":1166,"position":2},"title":"Get Table information (Column Name, Data Type) in SQL Server","author":"Jitendra","date":"September 18, 2010","format":false,"excerpt":"Get Table information like Column Name, Data Type, Character length, Default Values etc in SQL Server","rel":"","context":"In &quot;SQL Server&quot;","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"SQL Server INFORMATION_SCHEMA.COLUMNS","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/09\/SQL-Server-INFORMATION_SCHEMA.COLUMNS-300x44.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":6449,"url":"https:\/\/www.jitendrazaa.com\/blog\/salesforce\/salesforce-integration-with-sql-server-integration-services\/","url_meta":{"origin":1166,"position":3},"title":"Salesforce Integration with SQL Server Integration Services","author":"Jitendra","date":"April 15, 2018","format":false,"excerpt":"Video - Connecting SQL Server Integration Services (SSIS) with Salesforce using COZYROC Connector","rel":"","context":"In &quot;Salesforce&quot;","block_context":{"text":"Salesforce","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/salesforce\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/img.youtube.com\/vi\/RA5azDuubU4\/0.jpg?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":448,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/search-for-table-in-sql-server\/","url_meta":{"origin":1166,"position":4},"title":"Search for Table in SQL Server","author":"Jitendra","date":"June 30, 2010","format":false,"excerpt":"In\u00a0organization, where huge number of tables present in database. its very hard to search the particular table. Here few SQL queries to search the table name by pattern. Search For Table whose name starts with A [sourcecode lang=\"sql\"] SELECT DISTINCT [Table] = OBJECT_NAME(OBJECT_ID) FROM SYS.INDEXES WHERE OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1 AND\u2026","rel":"","context":"In &quot;SQL&quot;","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"List of Non clustered Tables in SQL Server 2005","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/06\/Non-clustered-Tables.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":829,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/convert-rows-into-comma-separated-values-column-sql-server\/","url_meta":{"origin":1166,"position":5},"title":"Convert rows into comma separated values column &#8211; SQL Server","author":"Jitendra","date":"October 4, 2010","format":false,"excerpt":"Convert rows into comma separated values column - SQL Server","rel":"","context":"In &quot;SQL Server&quot;","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/1166","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/comments?post=1166"}],"version-history":[{"count":0,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/1166\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/media?parent=1166"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/categories?post=1166"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/tags?post=1166"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}