View the number of online users currently connected in SQL Server

Source: Internet
Author: User
Tags bulk insert emit

 UseMasterSelectLoginame,Count(0) fromsysprocessesGroup  byloginameOrder  by Count(0)descSelectNt_username,Count(0) fromsysprocessesGroup  byNt_usernameOrder  by Count(0)desc

If one of the SQL Server user name Test connections is more, view the host name it came from:

Select hostname,count(0fromwhere loginame='  Test'group by hostnameorderbyCount (0desc

If a SQL Server user name Test connection is more, view the time range grouping for its last operation:

Select Convert(varchar, Last_batch,111),Count(0) fromsysprocesseswhereLoginame='Test'Group  by Convert(varchar, Last_batch,111)Order  by Count(0)desc

If you have more connections from the host (WWW), you can view its process details

Select *  from   where hostname='www'

If the WWW machine mainly provides Web services, it is possible that the ASP program handles the connection when there is a problem, generating the SQL statements that kill these processes:

Select '  '+convert(varcharfromwhere hostname= ' www '

If such problems occur frequently, you can write a stored procedure sp_killidlespids.sql,


write a job that executes it to automatically kill the user connected from the host (WWW) but has not responded for a day.
--------------------------------
Users and permissions for SQL Server
SysAdmin can perform any activity in SQL Server
ServerAdmin can set server-wide configuration options to shut down the server
Setupadmin can manage linked servers and startup processes
Securityadmin can manage logins and CREATE DATABASE permissions can also read error logs and change passwords
Processadmin can manage processes running in SQL Server
DBCreator can create changes and drop databases
Diskadmin can manage disk files
Bulkadmin can execute BULK INSERT statement
Fixed database role Description
DB_Owner has full permissions in the database
Db_accessadmin can add or remove user IDs
Db_securityadmin can manage all permissions object ownership roles and role memberships
Db_ddladmin can emit all DDL but cannot issue grant Revok or deny statements
Db_backupoperator can emit DBCC checkpoint and backup statements
Db_datareader can select all data from any user table in the database
Db_datawriter can change all data in any user table in the database
Db_denydatareader cannot select any data from any user tables in the database
Db_denydatawriter cannot change any data in any user tables in the database
Summary of extended storage in some SQL
Xp_availablemedia Display the drive letter ' C: ' Xp_availablemedia available on the system
Xp_enumgroups lists the usage groups for the current system and their descriptions xp_enumgroups
XP_ENUMDSN lists the ODBC data source names that are already set up on the system XP_ENUMDSN
Xp_dirtree displays subdirectories and file schemas under a directory xp_dirtree ' C:inetpubwwwroot '
Xp_getfiledetails get the related properties of a file Xp_getfiledetails ' c:inetpubwwwroot.asp '
Dbp.xp_makecab compress multiple files of the target computer into a file compressed files can be connected to the parameters of the following with the bean number separated dbp.xp_makecab ' c:lin.cab ', ' evil ', 1, ' c:inetpubmdb.asp '
Xp_unpackcab extract Xp_unpackcab ' C:hackway.cab ', ' c:temp ', 1
Xp_ntsec_enumdomains list server domain names Xp_ntsec_enumdomains
Xp_servicecontrol stop or start a service Xp_servicecontrol ' stop ', ' schedule '
Xp_terminate_process use PID to stop a program in execution xp_terminate_process 123
Dbo.xp_subdirs only the subdirectories under a directory dbo.xp_subdirs ' C:

Original source: http://liucb.blog.51cto.com/3230681/1579452

View the number of online users currently connected in SQL Server

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.