solorez~Z Space

关注数据库,关注MS SQL Server

导航

直接生成服务器安全设置报告的脚本

老外真是细心,写了份这么有用的脚本,可以生成数据库所有用户及权限的报告,直接生成网页代码另存为.htm即可,代码如下:

Code

生成报告的效果如下:

Sample Report

Save the result from the query above in an .html file and open it with an Internet Browser.

The report from the Browser will look something like this, but should have information for all of your databases.

Server MyServer\SQL_2005
 
Logins information
Login Name Default DB Language Denied acess? Windows Auth? Window group? Date created Date updated Server roles
##MS_AgentSigningCertificate## master us_english -- -- -- Jan 16 2009 2:32PM Jan 16 2009 2:32PM --
##MS_SQLAuthenticatorCertificate## master -- -- -- -- Jan 16 2009 2:31PM Jan 16 2009 2:31PM --
##MS_SQLReplicationSigningCertificate## master -- -- -- -- Jan 16 2009 2:31PM Jan 16 2009 2:31PM --
##MS_SQLResourceSigningCertificate## master -- -- -- -- Jan 16 2009 2:31PM Jan 16 2009 2:31PM --
MyServer\Michelle2 Michelle us_english -- X -- May 30 2009 3:06PM May 30 2009 3:06PM securityadmin
MyServer\SQLServer2005MSFTEUser$MyServer master us_english -- X X Jan 16 2009 2:18PM Jan 16 2009 2:18PM --
MyServer\SQLServer2005MSSQLUser$MyServer master us_english -- X X Jan 16 2009 2:18PM Jan 16 2009 2:18PM sysadmin
MyServer\SQLServer2005SQLAgentUser$MyServer master us_english -- X X Jan 16 2009 2:18PM Jan 16 2009 2:18PM sysadmin
MyServer\TestGroup Michelle us_english -- X X May 30 2009 3:08PM Aug 9 2009 11:49PM diskadmin,dbcreator
MyServer\User master us_english -- X -- Jun 29 2009 1:20PM Jun 29 2009 1:20PM sysadmin
BUILTIN\Administrators master us_english -- X X Jan 16 2009 2:18PM Jan 16 2009 2:18PM sysadmin
NT AUTHORITY\SYSTEM master us_english -- X -- Jan 16 2009 2:18PM Jan 16 2009 2:18PM sysadmin
sa master us_english -- -- -- Apr 8 2003 9:10AM Jan 16 2009 2:18PM sysadmin

Back To Top ^
 

Database test
Mapping of logins to users
Login Name User Name
MyServer\Michelle2 MyServer\Michelle2
MyServer\TestGroup MyServer\TestGroup

Back To Top ^

Roles per user
Role Name User Name
db_datareader MyServer\TestGroup
db_denydatawriter MyServer\TestGroup

Back To Top ^

Database level permissions
User Name Permission type Permission Name Grant option?
MyServer\Michelle2 GRANT CONNECT --
MyServer\TestGroup GRANT CONNECT --
dbo GRANT CONNECT --

Back To Top ^

Object permissions
User Name Permission type Permission Name Schema Name Object Name Object type type Column Name Grant option?
MyServer\Michelle2 DENY DELETE dbo Database_Info Table -- --
MyServer\Michelle2 DENY VIEW DEFINITION dbo MyTestProc Stored Proc -- --
MyServer\Michelle2 GRANT EXECUTE dbo MyTestProc Stored Proc -- --
MyServer\Michelle2 GRANT SELECT dbo Database_Info Table -- --
MyServer\Michelle2 GRANT DELETE dbo Backup_Information Table -- X
MyServer\Michelle2 GRANT INSERT dbo Backup_Information Table -- X
MyServer\TestGroup DENY DELETE dbo tbl_Servers Table -- --
MyServer\TestGroup DENY SELECT dbo SQL_Servers Table -- --
MyServer\TestGroup GRANT ALTER dbo tbl_Servers Table -- --
MyServer\TestGroup GRANT DELETE dbo Backup_Information Table -- --
MyServer\TestGroup GRANT INSERT dbo Backup_Information Table -- --
MyServer\TestGroup GRANT INSERT dbo tbl_Servers Table -- --
MyServer\TestGroup GRANT SELECT dbo tbl_Servers Table -- --
MyServer\TestGroup GRANT SELECT dbo Backup_Information Table -- --

Back To Top ^

posted on 2009-08-18 18:53  付博  阅读(566)  评论(0编辑  收藏  举报