SQLServer高阶应用.docVIP

SQL Server 高阶应用 Posted on 2005-06-06 12:51 Lets DotNet 阅读(413) 评论(1) 编辑 收藏 网摘 所属分类: tech 一、统计功能 [查看当前SQLServer的使用人员列表] select count(distinct loginame) from sysprocesses 注- sysprocesses 是系统表，在默认数据库master中 [查看当前SQLServer的使用人数] select distinct loginame from sysprocesses [过滤查询] 新建一张专门用于监视数据连接使用情况的临时表 CREATE table MonitorUser ( spid int, ecid int, status varchar(200), loginame varchar(200), hostname varchar(200), --根据sp_who的结果新建表结构 blk varchar(200), dbname varchar(200), cmd varchar(200), [DateTime] [datetime] NOT NULL ) ALTER TABLE [dbo].[MonitorUser] WITH NOCHECK ADD CONSTRAINT [DateTime] DEFAULT (getdate()) for[DateTime] --自动获取时间插到datetime列中 GO insert into MonitorUser(spid,ecid,status,loginame,hostname,blk,dbname,cmd) exec (sp_who) 例：select * from MonitorUser where hostname=XXX-DAVID-J-GU and dbname=PRJ2 [Status解释] Background: 后台执行（SPID is performing a background task.） Sleeping: 休眠（SPID is not currently executing. This usually indicates that the SPID is awaiting a command from the application）. Runnable: 正在执行（SPID is currently executing）. Dormant: 休眠并清除资源（Same as Sleeping, except Dormant also indicates that the SPID has been reset after completing an RPC event. The reset cleans up resources used during the RPC event. This is a normal state and the SPID is available and waiting to execute further commands）. Rollback: 回滚（The SPID is in rollback of a transaction）. Defwakeup: 等待资源（Indicates that a SPID is waiting on a resource that is in the process of being freed. The waitresource field should indicate the resource in question）. Spinloop: 等待解锁（Process is waiting while attempting to acquire a spinlock used for concurrency control on SMP systems）. 二、SQL注入 [xp_cmdshell] 许多存储过程被创建在SQLSERVER中，执行各种各样的功能，例如发送电子邮件和与注册表交互。 Xp_cmdshell是一个允许执行任意的命令行命令的内置的存储过程。例如： Exec