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!
Best Answer
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.