A New Hope
SQL Server Management Studio is a phenomenal tool, especially when you want to script out complex tasks. Do whatever you want in the GUI and click the handy little script button instead of OK and you’ve got a set of raw SQL that you can modify/reuse/etc. This process works for a good majority of the operations you will find yourself doing as a DBA, but there’s one gaping hole that MS has yet to fill: scripting out Database Roles. If you manage any level of database security, chances are you are using Database Roles. If you were lucky enough to create them yourself, it’s not difficult to keep a script library to see what role allows for what permission(s). However, the moment you inherit a database that contains custom Database Roles, you enter a world of hurt. Traditionally, you can either painstakingly struggle with the GUI trying to associate various records and checkboxes with actual commands or you can throw in the towel and start from scratch. Thankfully, there is a new way, as I’ve scripted out a process that will generate the role definition for you.
John Eisbrener, DBA 