I wrote this procedure mainly as an exercise in converting varbinary to a usable string in a CTE, but it ended up being a suitable replacement for a procedure (two procedures, actually) that have annoyed me for a while.
Here are the reasons I like this script better than sp_help_revlogin:
- One procedure instead of two.
- Does not have to exist in the master database.
- Scripts "creating message" output AS WELL AS whether or not the creation succeeded so you can tell where it actually had an error if there is one.
- Checks for the existence of the login BEFORE it tries to create it.
- States whether or not the login already existed or was created.
- Allows you script logins for a single database as opposed to the whole server.
- Allows you to script logins with [master] as the default database in case you are preparing a server where the databases do not exist for a given login.
- Uses CTEs for the password and SID hash (welcome to 2005).
Hope this is useful to someone, good luck and enjoy.
