snippetsqlMinor
How to export all the security-related information from a SQL Server database
Viewed 0 times
theallsqlrelateddatabasesecurityexporthowserverfrom
Problem
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
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!
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!
Solution
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
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
Context
StackExchange Database Administrators Q#58772, answer score: 9
Revisions (0)
No revisions yet.