View the number of online users currently connected in SQL Server

Use master
Select Loginame,count (0) from sysprocesses
GROUP BY Loginame
Order by count (0) desc

Select Nt_username,count (0) from sysprocesses
GROUP BY Nt_username
Order 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 (0) from sysprocesses where loginame= ' test '
GROUP BY hostname
Order by count (0) desc

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) from sysprocesses where loginame= ' 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 sysprocesses 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 ' Kill ' +convert (varchar,spid) from sysprocesses where 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:

This article is from the "strive to the front" blog, please be sure to keep this source http://liucb.blog.51cto.com/3230681/1579452

View the number of online users currently connected in SQL Server