sqlserver创建用户和授权操作.docxVIP

登录需要被监控的数据库服务器。通过管理员的身份登录到需要监控的sqlserver数据库，例如通过SA登录。添加监控用的数据库用户并授予相关权限。登录后打开“新建查询”窗口，将下面创建用户授权的sql脚本拷贝到窗口中执行。execsp_addlogin sqlmonitor,sqlmonitor2012,masterexecsp_addrole r_sqlmonitorgogrant select on sysfiles to r_sqlmonitorgrant exec on dbo.sp_monitor to r_sqlmonitorgrant select on master.dbo.sysprocesses to r_sqlmonitorgrant select on sysdatabases to r_sqlmonitorgrant select on sysprocesses to r_sqlmonitorgrant select on master.dbo.syslockinfo to r_sqlmonitorgrant select on master.dbo.sysprocesses to r_sqlmonitorgrant select on master.dbo.spt_values to r_sqlmonitorgrant select on sysfiles to r_sqlmonitorgrant select on sysfilegroups to r_sqlmonitorgrant exec on sp_databases to r_sqlmonitorgrant exec on sp_spaceused to r_sqlmonitorgrant exec on sp_databases to r_sqlmonitorgrant select on dbo.sysfiles to r_sqlmonitorgrant select on dbo.sysfilegroups to r_sqlmonitorgrant select on master.dbo.sysprocesses to r_sqlmonitorgoexecsp_grantdbaccess sqlmonitor,sqlmonitorexecsp_addrolemember r_sqlmonitor,sqlmonitorgoexecsp_addsrvrolemember @loginame = Nsqlmonitor, @rolename = Nsysadmingo执行结果如下，下方消息窗口中会显示“命令已成功完成”：验证用户是否创建成功，并且授予了必要的权限。退出SA登录，使用sqlmonitor用户进行登录，登录后在“新建查询”窗口中执行下面的脚本。select * from sysfilesexecsp_monitorselect * from master.dbo.sysprocessesselect * from sysdatabasesselect * from sysprocessesselect * from master.dbo.syslockinfoselect * from master.dbo.sysprocessesselect * from master.dbo.spt_valuesselect * from sysfilesselect * from sysfilegroupscreate table #procpages(id varchar(2))drop table #procpagescreate table #metadata(id varchar(2))drop table #metadatacreate table #dbspace(id varchar(2))drop table #dbspacecreate table #file_count(id varchar(2))drop table #file_countexecsp_databasesexecsp_spaceusedexecsp_databasesselect * from dbo.sysfilesselect * from dbo.sysfilegroupsselect * from master.dbo.sysprocessesgo执行结果如下：检查消息窗口是否有错误，有错误的话会有红色的错误提示信息：到这里，如果没出现红色错误提示信息，表明用户创建成功，下面的第4步就不用执行了。如果执行过程中出现错误，请按下面方法执行。主要用来解决操作过程中不小心导致执行不成功的问题。通过SA用户登录，执行下面的脚本来删除上面建立的用户信