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. Commented 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). Commented Feb 28, 2017 at 18:05
  • What's interesting to me is SMO doesn't seem to support this (or at least not easily). Commented Jan 5, 2021 at 0:42
3

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.” Commented 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. Commented Jan 3, 2023 at 10:26

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.