主页 / dba / 问题 / 58772
Accepted
Martin Surasky
Asked: 2014-02-13 10:45:24 +0800 CST

如何从 SQL Server 数据库中导出所有与安全相关的信息

  • 772

你们可能知道，SQL Server 没有提供开箱即用的解决方案来导出为数据库中每个元素声明的所有安全相关语句（在数据库级别和对象级别）

我说的是想出这些信息的能力

  • 所有用户
  • 所有用户定义的角色
  • 数据库级别的所有权限（例如 GRANT CREATE FUNCTION）
  • 对象级别的所有权限（例如 GRANT SELECT ON OBJECT::xxx）

你会认为 SQL Server 必须有这样的东西，但是 SQL Server 导出向导或由于右键单击对象而生成的各种脚本都不会捕获此信息。

我在网上看到了许多不同的“可能的解决方案”，使用人们慷慨发布的非策划脚本，但由于我必须 100% 确保所有安全信息都被捕获，所以我不能完全依赖这些脚本。

我可以选择以这些为起点来自己写一些东西，但我讨厌不得不重新发明轮子来满足你认为很多人可能有的要求。

是否有人提供的工具（作为 SQL Server 产品的一部分或第 3 方工具）可以可靠地为您提供此信息？

或者至少，是否有一个社区支持的脚本，大多数人会同意“将完成这项工作”？

谢谢！

sql-server security

3 个回答

  1. 很抱歉您从昨天开始就没有回复；这至少是你的一个起点。

    你可以试着把你需要的碎片拉出来。和往常一样，阅读讨论线程（很遗憾，我找不到我认识的大牌认可的脚本，所以要彻底测试！模式、对象、服务器和列级别的权限经常丢失）。

    我实际上并没有使用任何这些，但它们是一组起点，没有特定的顺序。

    列出所有数据库中的所有权限

    使用 Powershell 和 T-SQL 进行自动权限审计：第 1 部分

    所有数据库的权限脚本

    脚本数据库级别权限 v2.1

    脚本化用户权限的存储过程

    安全查询：数据库级别

    安全查询：服务器级

    • 9
  2. Best Answer

    Idera SQL Permissions Extractor似乎是您正在寻找的产品。

    它可以编写服务器和对象权限脚本，并且是免费的。还有一个商业版，叫做SQL Secure，它有更多的功能。

    两个版本之间的功能比较可以在这里找到。

    • 6

  3. https://gallery.technet.microsoft.com/Extract-Database-dfa53d5a/

    请参阅该链接以下载以下代码

    set nocount off

IF OBJECT_ID(N'tempdb..##temp1') IS NOT NULL
     DROP TABLE ##temp1

create table ##temp1(query varchar(1000))

insert into ##temp1 
select 'use '+db_name() +';'

insert into ##temp1 
select 'go'

/*creating database roles*/
insert into ##temp1
                    select 'if DATABASE_PRINCIPAL_ID('''+name+''')  is null 
                    exec sp_addrole '''+name+''''  from sysusers
where issqlrole = 1 and (sid is not null and sid <> 0x0)

/*creating application roles*/
insert into ##temp1
                    select 'if DATABASE_PRINCIPAL_ID('+char(39)+name+char(39)+')
                    is null CREATE APPLICATION ROLE ['+name+'] WITH DEFAULT_SCHEMA = ['+
                    default_schema_name+'], Password='+char(39)+'Pass$w0rd123'+char(39)+' ;'
 from sys.database_principals
where type_desc='APPLICATION_ROLE'

insert into ##temp1 
                     select  
                                case  
                                          when state_desc='GRANT_WITH_GRANT_OPTION' 
                                                       then
                                                                substring (state_desc,0,6)+' '+permission_name+' to '+'['+USER_NAME(grantee_principal_id)+']'+' WITH GRANT OPTION ;'

                                                         else 
                                                                  state_desc+' '+permission_name+' to '+'['+USER_NAME(grantee_principal_id)+']'+' ;'
                    END
from sys.database_permissions 
where class=0 and USER_NAME(grantee_principal_id) not in ('dbo','guest','sys','information_schema')

insert into ##temp1 
                    select 
                               case 
                                         when state_desc='GRANT_WITH_GRANT_OPTION' 
                                                   then
                                                             substring (state_desc,0,6)+' '+permission_name+' on '+OBJECT_SCHEMA_NAME(major_id)+'.['+OBJECT_NAME(major_id)
                                                             +'] to '+'['+USER_NAME(grantee_principal_id)+']'+' with grant option ;'
                                                     else 
                                                              state_desc+' '+permission_name+' on '+OBJECT_SCHEMA_NAME(major_id)+'.['+OBJECT_NAME(major_id)
                                                              +'] to '+'['+USER_NAME(grantee_principal_id)+']'+' ;'
                                  end
from sys.database_permissions where class=1 and USER_NAME(grantee_principal_id) not in ('public');


 insert into ##temp1 
                      select 
                                 case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION' 
                                                     then
                                                              substring (state_desc,0,6)+' '+permission_name+' ON schema::['+sa.name+
                                                               '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                                       else
                                                               state_desc+' '+permission_name+' ON schema::['+sa.name+
                                                               '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                       COLLATE LATIN1_General_CI_AS  
                                      end
from sys.database_permissions dp inner join sys.schemas sa on
 sa.schema_id = dp.major_id where dp.class=3

 insert into ##temp1 
                     select 
                                 case 
                                            when state_desc='GRANT_WITH_GRANT_OPTION'
                                             then
                                                    substring (state_desc,0,6)+' '+permission_name+' ON APPLICATION  ROLE::['+sa.name+
                                                     '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                             else
                                                      state_desc+' '+permission_name+' ON  APPLICATION ROLE::['+sa.name+
                                                      '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                      COLLATE LATIN1_General_CI_AS  
                         end
from sys.database_permissions dp inner join sys.database_principals  sa on
 sa.principal_id = dp.major_id where dp.class=4 and sa.type='A'

 insert into ##temp1 
                      select 
                                 case 
                                          when state_desc='GRANT_WITH_GRANT_OPTION' 
                                           then
                                                  substring (state_desc,0,6)+' '+permission_name+' ON   ROLE::['+sa.name+
                                                  '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                           else
                                                   state_desc+' '+permission_name+' ON   ROLE::['+sa.name+
                                                    '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                     COLLATE LATIN1_General_CI_AS  
                                           end
 from sys.database_permissions dp inner join
sys.database_principals  sa on sa.principal_id = dp.major_id 
 where dp.class=4 and sa.type='R'

 insert into ##temp1 
                      select 
                                  case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION' 
                                                       then
                                                               substring (state_desc,0,6)+' '+permission_name+' ON ASSEMBLY::['+sa.name+
                                                                '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                                        else
                                                                state_desc+' '+permission_name+' ON ASSEMBLY::['+sa.name+
                                                                 '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                                 COLLATE LATIN1_General_CI_AS  
                                       end
 from sys.database_permissions dp inner join sys.assemblies sa on
 sa.assembly_id = dp.major_id 
 where dp.class=5

 insert into ##temp1
                     select 
                                 case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION' 
                                            then
                                                    substring (state_desc,0,6)+'  '+permission_name+' ON type::['
                                                    +SCHEMA_NAME(schema_id)+'].['+sa.name+
                                                    '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                            else
                                                    state_desc+' '+permission_name+' ON type::['
                                                    +SCHEMA_NAME(schema_id)+'].['+sa.name+
                                                     '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                     COLLATE LATIN1_General_CI_AS  
                                              end
 from sys.database_permissions dp inner join sys.types sa on
 sa.user_type_id = dp.major_id 
 where dp.class=6


 insert into ##temp1
                      select 
                                 case 
                                          when state_desc='GRANT_WITH_GRANT_OPTION' 
                                           then
                                                     substring (state_desc,0,6)+'  '+permission_name+' ON  XML SCHEMA COLLECTION::['+
                                                     SCHEMA_NAME(SCHEMA_ID)+'].['+sa.name+'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                            else
                                                     state_desc+' '+permission_name+' ON  XML SCHEMA COLLECTION::['+
                                                     SCHEMA_NAME(SCHEMA_ID)+'].['+sa.name+'] to ['+user_name(dp.grantee_principal_id)+'];'
                                                     COLLATE LATIN1_General_CI_AS  
                                   end
 from sys.database_permissions dp inner join sys.xml_schema_collections sa on
 sa.xml_collection_id = dp.major_id 
 where dp.class=10



insert into ##temp1
                    select
                               case 
                                         when state_desc='GRANT_WITH_GRANT_OPTION' 
                                          then
                                                   substring (state_desc,0,6)+'  '+permission_name+' ON message type::['+sa.name+
                                                    '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                           else
                                                    state_desc+' '+permission_name+' ON message type::['+sa.name+
                                                    '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                     COLLATE LATIN1_General_CI_AS  
                                             end
 from sys.database_permissions dp inner join sys.service_message_types sa on
 sa.message_type_id = dp.major_id 
 where dp.class=15


 insert into ##temp1
                      select 
                                  case 
                                            when state_desc='GRANT_WITH_GRANT_OPTION' 
                                              then
                                                       substring (state_desc,0,6)+'  '+permission_name+' ON contract::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                                else
                                                         state_desc+' '+permission_name+' ON contract::['+sa.name+
                                                         '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                         COLLATE LATIN1_General_CI_AS  
                                   end
 from sys.database_permissions dp inner join sys.service_contracts sa on
 sa.service_contract_id = dp.major_id 
 where dp.class=16



  insert into ##temp1
                      select 
                                 case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION' 
                                            then
                                                      substring (state_desc,0,6)+'  '+permission_name+' ON SERVICE::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                              else
                                                       state_desc+'  '+permission_name+' ON SERVICE::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                        COLLATE LATIN1_General_CI_AS  
                                    end
 from sys.database_permissions dp inner join sys.services sa on
 sa.service_id = dp.major_id 
 where dp.class=17


 insert into ##temp1 
                      select 
                                   case 
                                              when state_desc='GRANT_WITH_GRANT_OPTION'
                                               then
                                                          substring (state_desc,0,6)+'  '+permission_name+' ON REMOTE SERVICE BINDING::['+sa.name+
                                                          '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                                 else
                                                          state_desc+' '+permission_name+' ON REMOTE SERVICE BINDING::['+sa.name+
                                                           '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                          COLLATE LATIN1_General_CI_AS  
                                      end
 from sys.database_permissions dp inner join sys.remote_service_bindings sa on
 sa.remote_service_binding_id = dp.major_id 
 where dp.class=18

 insert into ##temp1
                      select
                                  case 
                                            when state_desc='GRANT_WITH_GRANT_OPTION'
                                              then
                                                        substring (state_desc,0,6)+'  '+permission_name+' ON route::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                                else
                                                          state_desc+' '+permission_name+' ON route::['+sa.name+
                                                          '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                         COLLATE LATIN1_General_CI_AS  
                                      end
 from sys.database_permissions dp inner join sys.routes sa on
 sa.route_id = dp.major_id 
 where dp.class=19

 insert into ##temp1 
                      select 
                                 case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION' 
                                            then
                                                     substring (state_desc,0,6)+'  '+permission_name+' ON FULLTEXT CATALOG::['+sa.name+
                                                      '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                             else
                                                       state_desc+' '+permission_name+' ON FULLTEXT CATALOG::['+sa.name+
                                                       '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                        COLLATE LATIN1_General_CI_AS  
                                       end
 from sys.database_permissions dp inner join sys.fulltext_catalogs sa on
 sa.fulltext_catalog_id = dp.major_id 
 where dp.class=23

  insert into ##temp1 
                      select 
                                 case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION'
                                            then
                                                        substring (state_desc,0,6)+'  '+permission_name+' ON SYMMETRIC KEY::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                             else
                                                        state_desc+' '+permission_name+' ON SYMMETRIC KEY::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                        COLLATE LATIN1_General_CI_AS  
                                             end
 from sys.database_permissions dp inner join sys.symmetric_keys sa on
 sa.symmetric_key_id = dp.major_id 
 where dp.class=24

 insert into ##temp1 
                      select 
                                  case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION' 
                                             then
                                                       substring (state_desc,0,6)+'  '+permission_name+' ON certificate::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                               else
                                                          state_desc+' '+permission_name+' ON certificate::['+sa.name+
                                                          '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                           COLLATE LATIN1_General_CI_AS  
                                   end
 from sys.database_permissions dp inner join sys.certificates sa on
 sa.certificate_id = dp.major_id 
 where dp.class=25


 insert into ##temp1 
                     select 
                                 case 
                                          when state_desc='GRANT_WITH_GRANT_OPTION' 
                                          then
                                                     substring (state_desc,0,6)+'  '+permission_name+' ON ASYMMETRIC KEY::['+sa.name+
                                                     '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                             else
                                                      state_desc+' '+permission_name+' ON ASYMMETRIC KEY::['+sa.name+
                                                       '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                       COLLATE LATIN1_General_CI_AS  
                        end
 from sys.database_permissions dp inner join sys.asymmetric_keys sa on
 sa.asymmetric_key_id = dp.major_id 
 where dp.class=26

insert into ##temp1 
                     select  'exec sp_addrolemember ''' +p.NAME+''','+'['+m.NAME+']'+' ;'
FROM sys.database_role_members rm
JOIN sys.database_principals p
ON rm.role_principal_id = p.principal_id
JOIN sys.database_principals m
ON rm.member_principal_id = m.principal_id
where m.name not like 'dbo';





select *  from ##temp1
    • 2

相关问题