-- 查看性能记数器

 
 
 
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;