Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
The stuff that @Anti-weakpasswords mentioned - Script DB Level Permissions v2.1 will do the job. –  Kin yesterday
add comment

2 Answers

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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