sql server常用语句总结
http://ace105.blog.51cto.com/639741/792519
SQL Server 性能调优（一）——从等待状态判断系统资源瓶颈
http://blog.csdn.net/dba_huangzj/article/details/7607844
-- 查看性能记数器
SELECT * FROM sys.dm_os_performance_counters
-- 执行过的线程所遇到的所有等待(不是当前正在运行的线程, 为自上次重置统计信息或启动服务器以来累积的数据),可分析靠前的几个等待较高的事件。
select * from sys.dm_os_wait_stats order by wait_time_ms desc
该动态视图的细节，请查看帮助文档.
-- 重置该动态视图
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO
-- 正在等待某些资源的任务的等待队列
select * from sys.dm_os_waiting_tasks order by wait_duration_ms desc
内存使用：
查看当前由 SQL Server 分配的内存对象(KB): select sum((page_size_in_bytes/1024)*max_pages_allocated_count) from sys.dm_os_memory_objects;
查看系统内存当前信息: select * from sys.dm_os_sys_memory (这个动态视图只在sql 2008中才有)
select
cpu_count,
hyperthread_ratio,
scheduler_count,
physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,
virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,
bpool_committed * 8 / 1024 as bpool_committed_mb,
bpool_commit_target * 8 / 1024 as bpool_target_mb,
bpool_visible * 8 / 1024 as bpool_visible_mb
from sys.dm_os_sys_info
限制SQL Server使用的最小,最大内存(MB)：
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'min server memory', 300;
GO
sp_configure 'max server memory', 850;
GO
RECONFIGURE;
GO
CPU使用情况:
SELECT TOP 50
total_worker_time/execution_count AS '每次执行占用CPU(微秒)',
execution_count as '执行次数',
total_worker_time as '总共占用CPU(微秒)',
creation_time as '创建时间',
last_execution_time as '最后执行时间',
min_worker_time as '最低每次占用CPU',
max_worker_time as '最高每次占用cpu',
total_physical_reads as '总共io物理读取次数',
total_logical_reads as '总共逻辑读取次数',
total_logical_writes as '总共逻辑写次数',
total_elapsed_time as '完成此计划的执行所占用的总时间(微秒)',
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS 'SQL内容'
FROM sys.dm_exec_query_stats
ORDER BY 1 DESC
--下面的查询显示SQL 等待分析和前10 个等待的资源
select top 10 *
from sys.dm_os_wait_stats
where wait_type not in ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR')
order by wait_time_ms desc;
SQL Server中的活动会话数:
exec sp_who 'active'
print @@rowcount
SQL Server等待情况
select * from sys.dm_os_waiting_tasks ; --当前等待事件
select * from sys.dm_os_wait_stats --历史等待次数,是sqlserver启动后的累计值,需使用下一条语句清空
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
找出进程阻塞:
运行下面的查询可确定阻塞的会话
select blocking_session_id, wait_duration_ms, session_id from
sys.dm_os_waiting_tasks
where blocking_session_id is not null
spid 正在阻塞另一个 spid，可在数据库中创建以下存储过程，然后执行该存储过程。此存储过程会报告此阻塞情况。键入 sp_who 可找出 @spid；@spid 是可选参数。
create proc dbo.sp_block (@spid bigint=NULL)
as
select
t1.resource_type,
'database'=db_name(resource_database_id),
'blk object' = t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address and
t1.request_session_id = isnull(@spid,t1.request_session_id)
以下是使用此存储过程的示例。
exec sp_block
exec sp_block @spid = 7
select sum((page_size_in_bytes/1024)*max_pages_allocated_count) from sys.dm_os_memory_objects;
select * from sys.dm_os_sys_info;
select * from sys.dm_os_performance_counters
A. 获取有关按平均 CPU 时间排在最前面的五个查询的信息
以下示例返回前五个查询的 SQL 语句文本和平均 CPU 时间。
复制代码
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;
B. 提供批处理执行统计信息
以下示例返回按批执行的 SQL 查询的文本，并提供有关它们的统计信息。
复制代码
SELECT s2.dbid,
s1.sql_handle,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
execution_count,
plan_generation_num,
last_execution_time,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;
-- 数据库文件性能,文件io性能统计(必须重启sql server服务,才能清零该计数器)
select DB_NAME(database_id) DB_NAME, file_id,io_stall_read_ms ,num_of_reads
,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
,io_stall_write_ms,num_of_writes
,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
,io_stall_read_ms + io_stall_write_ms as io_stalls
,num_of_reads + num_of_writes as total_io
,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
from sys.dm_io_virtual_file_stats(null,null)
order by avg_io_stall_ms desc;
数据库名 文件ID
用户等待文件中发出读取所用的总时间（毫秒）
对文件发出的读取次数 平均读文件等待(毫秒)
用户等待在该文件中完成写入所用的总时间（毫秒） 在该文件中写入的次数
平均写文件等待毫秒 (读+写)等待毫秒
(读+写)次数 (读+写)平均等待毫秒
-- 数据库文件性能,文件io性能统计(必须重启sql server服务,才能清零该计数器),
-- 效果和"SQL Server 2005/2008 性能监控（待续）" 中 提到的一致。
SELECT DB_NAME(vfs.DbId) DatabaseName, mf.name,
mf.physical_name, vfs.BytesRead, vfs.BytesWritten,
vfs.IoStallMS, vfs.IoStallReadMS, vfs.IoStallWriteMS,
vfs.NumberReads, vfs.NumberWrites,
(Size*8)/1024 Size_MB
FROM ::fn_virtualfilestats(NULL,NULL) vfs
INNER JOIN sys.master_files mf ON mf.database_id = vfs.DbId
AND mf.FILE_ID = vfs.FileId
GO
-- 性能计数器动态视图, 和使用windows性能计数器效果一致。
SELECT [counter_name], [cntr_value] FROM sys.dm_os_performance_counters
WHERE ([instance_name] = '' OR [instance_name] = '_Total') AND (
([object_name] LIKE ('%Plan Cache%') AND [counter_name] IN
('Cache Hit Ratio', 'Cache Hit Ratio Base')) OR
([object_name] LIKE ('%Buffer Manager%') AND [counter_name] IN
('Buffer Cache Hit Ratio', 'Buffer Cache Hit Ratio Base', 'Page reads/sec', 'Page writes/sec')) OR
([object_name] LIKE ('%General Statistics%') AND [counter_name] IN
('Active Temp Tables', 'User Connections')) OR
([object_name] LIKE ('%Databases%') AND [counter_name] IN
('Transactions/sec', 'Log Cache Hit Ratio', 'Log Cache Hit Ratio Base', 'Log Flushes/sec',
'Log Bytes Flushed/sec', 'Backup/Restore Throughput/sec')) OR
([object_name] LIKE ('%Access Methods%') AND [counter_name] IN
('Full Scans/sec', 'Range Scans/sec', 'Probe Scans/sec', 'Index Searches/sec', 'Page Splits/sec')) OR
([object_name] LIKE ('%Memory Manager%') AND [counter_name] IN
('Target Server Memory (KB)', 'Target Server Memory(KB)', 'Total Server Memory (KB)')) OR
([object_name] LIKE ('%SQL Statistics%') AND [counter_name] IN
('SQL Compilations/sec', 'SQL Re-Compilations/sec'))
)
-- 查看分区表money，各个分区的行数和边界值.
select partition = $partition.分区函数名(userid)
,rows = count(*)
,minval = min(userid)
,maxval = max(userid)
from dbo.money with(nolock)
group by $partition.分区函数名(userid)
order by partition;
--查询某个数据库的连接数
select count(*) from Master.dbo.SysProcesses where dbid=db_id()
--前10名其他等待类型
SELECT TOP 10 *
from sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
SELECT *FROM sys.dm_os_wait_stats WHERE wait_type like 'PAGELATCH%'
OR wait_type like 'LAZYWRITER_SLEEP%'
--CPU的压力
SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
--表现最差的前10名使用查询
SELECT TOP 10 ProcedureName = t.text,
ExecutionCount = s.execution_count,
AvgExecutionTime = isnull ( s.total_elapsed_time / s.execution_count, 0 ),
AvgWorkerTime = s.total_worker_time / s.execution_count,
TotalWorkerTime = s.total_worker_time,
MaxLogicalReads = s.max_logical_reads,
MaxPhysicalReads = s.max_physical_reads,
MaxLogicalWrites = s.max_logical_writes,
CreationDateTime = s.creation_time,
CallsPerSecond = isnull ( s.execution_count / datediff ( second , s.creation_time, getdate ()), 0 )
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t ORDER BY
s.max_physical_reads DESC
SELECT SUM(signal_wait_time_ms) AS total_signal_wait_time_ms总信号等待时间 ,
SUM(wait_time_ms - signal_wait_time_ms) AS resource_wait_time_ms资源的等待时间,
SUM(signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS [signal_wait_percent信号等待％],
SUM(wait_time_ms - signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS [resource_wait_percent资源等待％]
FROM sys.dm_os_wait_stats
--一个信号等待时间过多对资源的等待时间那么你的CPU是目前的一个瓶颈。
--查看进程所执行的SQL语句
if (select COUNT(*) from master.dbo.sysprocesses) > 500
begin
select text,CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) from master.sys.sysprocesses a
end
select text,a.* from master.sys.sysprocesses a
CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
where a.spid = '51'
dbcc inputbuffer(53)
with tb
as
(
select blocking_session_id,
session_id,db_name(database_id) as dbname,text from master.sys.dm_exec_requests a
CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
),
tb1 as
(
select a.*,login_time,program_name,client_interface_name,login_name,cpu_time,memory_usage*8 as 'memory_usage(KB)',
total_scheduled_time,reads,writes,logical_reads
from tb a inner join master.sys.dm_exec_sessions b
on a.session_id=b.session_id
)
select a.*,connect_time,client_tcp_port,client_net_address from tb1 a inner join master.sys.dm_exec_connections b on a.session_id=b.session_id
--当前进程数
select * from master.dbo.sysprocesses
order by cpu desc
--查看当前活动的进程数
sp_who active
--查询是否由于连接没有释放引起CPU过高
select * from master.dbo.sysprocesses
where spid> 50
and waittype = 0x0000
and waittime = 0
and status = 'sleeping '
and last_batch < dateadd(minute, -10, getdate())
and login_time < dateadd(minute, -10, getdate())
--强行释放空连接
select 'kill ' + rtrim(spid) from master.dbo.sysprocesses
where spid> 50
and waittype = 0x0000
and waittime = 0
and status = 'sleeping '
and last_batch < dateadd(minute, -60, getdate())
and login_time < dateadd(minute, -60, getdate())
--查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)
select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master.dbo.sysprocesses order by cpu desc,physical_io desc
--查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局
SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text]
FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts,p.size_in_bytes desc
SELECT top 25 qt.text,qs.plan_generation_num,qs.execution_count,dbid,objectid
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt
WHERE plan_generation_num >1
ORDER BY qs.plan_generation_num
--cpu使用前50的语句
SELECT top 50 qt.text AS SQL_text ,SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
SUM(qs.total_worker_time)/SUM(qs.execution_count) AS avg_cpu_time,
COUNT(*) AS number_of_statements
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY qt.text
ORDER BY total_cpu_time DESC --统计总的CPU时间
--ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间
-- 计算可运行状态下的工作进程数量
SELECT COUNT(*) as workers_waiting_for_cpu,s.scheduler_id
FROM sys.dm_os_workers AS o
INNER JOIN sys.dm_os_schedulers AS s
ON o.scheduler_address=s.scheduler_address
AND s.scheduler_id<255
WHERE o.state='RUNNABLE'
GROUP BY s.scheduler_id
--表空间大小查询
create table #tb(表名 sysname,记录数 int,保留空间 varchar(100),使用空间 varchar(100),索引使用空间 varchar(100),未用空间 varchar(100))
insert into #tb exec sp_MSForEachTable 'EXEC sp_spaceused ''?'''
select * from #tb
go
SELECT
表名,
记录数,
cast(ltrim(rtrim(replace(保留空间,'KB',''))) as int)/1024 保留空间MB,
cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024 使用空间MB,
cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024/1024.00 使用空间GB,
cast(ltrim(rtrim(replace(索引使用空间,'KB',''))) as int)/1024 索引使用空间MB,
cast(ltrim(rtrim(replace(未用空间,'KB',''))) as int)/1024 未用空间MB
FROM #tb
WHERE cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024 > 0
--order by 记录数 desc
ORDER BY 使用空间MB DESC
DROP TABLE #tb
--查询是否由于连接没有释放引起CPU过高
select * from master.dbo.sysprocesses
where spid> 50
and waittype = 0x0000
and waittime = 0
and status = 'sleeping '
and last_batch < dateadd(minute, -10, getdate())
and login_time < dateadd(minute, -10, getdate())
--强行释放空连接
select 'kill ' + rtrim(spid) from master.dbo.sysprocesses
where spid> 50
and waittype = 0x0000
and waittime = 0
and status = 'sleeping '
and last_batch < dateadd(minute, -60, getdate())
and login_time < dateadd(minute, -60, getdate())
----查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)
select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master.dbo.sysprocesses order by cpu desc,physical_io desc
----查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局
SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text]
FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts,p.size_in_bytes desc
SELECT top 25 qt.text,qs.plan_generation_num,qs.execution_count,dbid,objectid
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt
WHERE plan_generation_num >1
ORDER BY qs.plan_generation_num
SELECT top 50 qt.text AS SQL_text ,SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
SUM(qs.total_worker_time)/SUM(qs.execution_count) AS avg_cpu_time,
COUNT(*) AS number_of_statements
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY qt.text
ORDER BY total_cpu_time DESC --统计总的CPU时间
--ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间
-- 计算可运行状态下的工作进程数量
SELECT COUNT(*) as workers_waiting_for_cpu,s.scheduler_id
FROM sys.dm_os_workers AS o
INNER JOIN sys.dm_os_schedulers AS s
ON o.scheduler_address=s.scheduler_address
AND s.scheduler_id<255
WHERE o.state='RUNNABLE'
GROUP BY s.scheduler_id
SELECT creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
, execution_count N'执行次数'
, total_worker_time/1000 N'所用的CPU总时间ms'
, total_elapsed_time/1000 N'总花费时间ms'
, (total_elapsed_time / execution_count)/1000 N'平均时间ms'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'执行语句'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like '%fetch%'
ORDER BY total_elapsed_time / execution_count DESC
-- 查看当前db的登陆
select * from sys.sql_logins
-- 审核登陆数据库的用户
sql server managerment studio中，右键点开服务器的属性，在安全性页签中， 选中审核“成功和失败的登陆”，所有登陆都会在..MSSQL/Log/ERRORLOG中记录一条记录。
如果勾选“启用C2审核跟踪”，将会在..MSSQL/Log/目录中，不断的增加audittrace201000502112519_15.trc文件，记录每一个对语句和对象的所有访问的审核. 可使用SQL Server Profiler打开查看。(需手动删除该类文件，否则磁盘必满。)
-- SQL Server Profiler
sys.traces
--------------------------------------------------------------------------------
仅编写存储过程不足以保证应用程序的安全，还应当考虑以下潜在的安全漏洞。
1.为您希望其能够访问数据的数据库角色授予对存储过程的 EXECUTE权限。
executeto 你的用户名)--取消"元数据<FONT color="#339966" face=""">"的任何权限
select,insert, delete, update,alter from public;
--创建数据库--创建表和存储过程等等对象
create,整个实例中唯一use mydb
exec,相当于给该数据库创建了一个映射用户。同login"登陆名<FONT color="green" face=""">。如有多个数据库，要执行多次grant,只有执行存储过程的权限
sp_addsrvrolemember'mydbuser','sysadmin';--添加服务角色,默认就有了。添加反而出错。
sp_addrolemember'db_owner','mydbuser'; --添加数据库角色,默认就有了。添加反而出错。
SQL Server 2005开始，引入了新的触发器类型。详细帮助文档"create trigger"。
本文描述通过触发器，限制某个数据库用户(比如 sa这种高级别的用户)，只能从指定的IP登陆，这可以做为防止非法连接数据库的最后防线。只有在建立数据库连接的最后一步（账号密码验证成功后）,才会激活这个触发器。
createtrigger tr_logincheckon all server for logon
as
if eventdata().value('(/EVENT_INSTANCE/LoginName)[1]','sysname') = 'sa'
andeventdata().value('(/EVENT_INSTANCE/ClientHost)[1]','varchar(15)') <> '192.168.0.23'
rollbacktran --会话退出,即:无法连接,登陆失败
go
下面这个示例，是可以记录sa登陆的信息，也可以根据需要，监控所有db用户的登陆信息
-- create table master.dbo.t_log_loginlog(loginame varchar(30),ipaddress varchar(40), spid int, hostname varchar(30), logtime datetime)
alter trigger tr_log_loginon all server WITH EXECUTEAS 'sa'
forlogon
as
declare@loginame varchar(30),
@ipaddress varchar(30),
@spid int,
@hostname varchar(30);
select@loginame = eventdata().value('(/EVENT_INSTANCE/LoginName)[1]','sysname'),
@ipaddress = eventdata().value('(/EVENT_INSTANCE/ClientHost)[1]','sysname'),
@spid = eventdata().value('(/EVENT_INSTANCE/SPID)[1]','int');
if @loginame ='sa' or @loginame = 'jav'
begin
select @hostname = [host_name] fromsys.dm_exec_sessionswhere session_id = @spid;
insert into master.dbo.t_log_loginlog(loginame,ipaddress,spid,hostname,logtime)
values( @loginame,@ipaddress,@spid,@hostname,getdate() );
end
go
-- sql server 2005/2008 用户授权
1. 如果要在一个db下面，各个用户使用自已的对象，最好的方法是建多个Schema(中文意思为架构,或模式), 每个用户为自已对应架构的“所有者”，可以在"服务器 > 数据库 > 你的业务DB > 安全性 > 架构"中新建.
2. 如果a用户,需要访问b用户的架构(Schema)对象，可以用grant select to a; 如果要执行b用户架构下的存储过程，或函数, 触发器等，则除了要 grant execute to a之外，在b用户的创建过程ddl语句中，还要指定: WITH EXECUTE AS N'用户a'， 举一个实际应用的例子。
-- 以ddl语句dbo_sp_helptext的执行权限给某特定的用户就可以了，它就可以查看别的用户的对象源码，但无法修改或删除别的用户的对象createas
executesp_helptext @objname;
create WITHEXECUTE AS N'dbo'
begin
if(charindex('&',@cmd)> 0)
print error:不允许执行多条命令;
end
if (charindex('dir',rtrim(ltrim(@cmd))) = 1)
begin
execute @i_result= master..xp_cmdshell@cmd,@no_output;
end
begin
return -1;
end
go
3. 如果A用户,需要创建自已的 job作业, 同时还需要查看其它用户的SQL Server Agent job信息 (SQL Server 代理 作业)，不能修改和删除别人的作业，则可以给它赋 msdb数据库的 "SQLAgentReaderRole"权限. 具体参见“联机帮助”
[sql] view plaincopy
-- 导入用户数据库中的 用户及角色权限
select 'CREATE USER [' + a.name+'] FOR LOGIN [' + b.name + ']'
from sys.database_principals a inner join sys.server_principals b
on a.sid=b.sid
select 'exec sp_addrolemember ''' + (select top 1 name from sys.database_principals where principal_id = s.role_principal_id)
+ ''', ''' + (select top 1 name from sys.database_principals where principal_id = s.member_principal_id) + ''';'
from sys.database_role_members s
where member_principal_id > 4;