View the number of online users currently connected in SQL Server

 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