sql server常用语句总结

— 查看性能记数器

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);

— 正在等待某些资源的任务的等待队列

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;

RECONFIGURE;

sp_configure 'min server memory', 300;

sp_configure 'max server memory', 850;

RECONFIGURE;

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)

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

— 性能计数器动态视图, 和使用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

(

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

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

if eventdata().value('(/EVENT_INSTANCE/LoginName)[1]',’sysname') = 'sa'

andeventdata().value('(/EVENT_INSTANCE/ClientHost)[1]',’varchar(15)') <> '192.168.0.23'

rollbacktran --会话退出,即:无法连接,登陆失败

下面这个示例，是可以记录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

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

— 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

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;

from:http://ace105.blog.51cto.com/639741/792519

November 2024
M	T	W	T	F	S	S
« Oct
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30