14

As you guys probably know, SQL Server does not provide an out-of-the-box solution to export all the security related statements that are declared for each element in the database (both at the database level and also the object level)

I'm talking about the ability to come up with this information

  • All the users
  • All the user-defined roles
  • All permissions at database level (e.g. GRANT CREATE FUNCTION)
  • All permissions at the object level (e.g. GRANT SELECT ON OBJECT::xxx)

You would think that SQL Server MUST have something like this but neither the SQL Server Export Wizard or the various scripts that are generated as a result of right clicking the objects do capture this information.

I have seen online many different "possible solutions" using non-curated scripts that people graciously post, but since I have to be 100% sure that all security information is captured I can't fully rely on those scripts.

I have the option of using those as a starting point to write something myself but I hate having to re-invent the wheel for a requirement that you would think many people may have.

Isn't there a tool provided by someone (either as part of the SQL Server product or an 3rd party tool) that could reliably provide you with this information?

Or at least, is there a community supported script that most of people would agree "will do the job"?

Thanks!

Improve this question
0

4 Answers 4

Reset to default
6

Idera SQL Permissions Extractor seems to be the product you're looking for.

It can script server and object permissions and it is free. There is also a commercial edition, called SQL Secure which has more features.

The feature comparison between the two editions can be found here.

Improve this answer
1
  • Thanks @spaghettidba I'll mark your response and accept your answer as it is likely easier for me to implement extraction using a single tool that compile a list of scripts that are not fully endorsed.
    – Martin Surasky
    Feb 28, 2017 at 18:04
9

I'm sorry you haven't had a response since yesterday; here's at least a starting point for you.

You can try pulling the pieces you need out of. As always, read the discussion threads (I was, regrettably, unable to find a script endorsed by the big names I recognize, so test thoroughly! Schema, object, server, and column level permissions are often missing).

I haven't actually used any of these, but they're a set of starting points, in no particular order.

List all permission in all databases

Automated Permissions Auditing With Powershell and T-SQL: Part 1

Permission scripting over all databases

Script DB Level Permissions v2.1

Stored procedure to script out user permissions

Security Queries: Database-level

Security Queries: Server-level

Improve this answer
2
  • Thanks, Even though I can't accept 2 answers I still appreciate this complication of T-SQL Scripts, maybe this is also going to help with my prob (even though I'll try Idera first, as it seems easier and likely will yield better results).
    – Martin Surasky
    Feb 28, 2017 at 18:05
  • What's interesting to me is SMO doesn't seem to support this (or at least not easily).
    – John Zabroski
    Jan 5, 2021 at 0:42
2

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

refer that link to download the below code

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
Improve this answer
0

i used these commands and the export went without problems

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/security/transfer-logins-passwords-between-instances

Improve this answer
2
  • “While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes.”
    – Rohit Gupta
    Jan 3, 2023 at 10:26
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Jan 3, 2023 at 10:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.