Over the years, I have used countless scripts and applications to generate all permissions for SQL Server databases. I have used both paid and free tools. I feel that not one of them seems to be accurate in every instance. I am simply looking for a way to generate all permissions for a given database along with confidence that the script is correct and able to be used after a backup/restore from another instance. Does anyone have a method they have confidence in and use regularly
I have a couple of stored procedures that I wrote and use on a regular basis.
They each have three outputs.
Principals: A list of the database/server principals and associated info including create/drop scripts.
Role membership: A list of what roles each database/server principal belongs to along with add/drop scripts.
Permissions: A list of individual permissions (EXECUTE, INSERT, UPDATE, CONNECT etc) for each database/server principal.
Obviously the Srv sp is for the Server principals and the DB sp is for the database level principals. The DB sp also has an 'All' option for the database name. With this option it runs through and pulls all permissions for all databases.
Note: This was originally intended as a research tool so there are a fair number of options that will let you parse down your data. Just roles, everything but roles, just a specific principal name etc.
Worked reasonably well for me this past summer.