Often, it seems to happen that security-related objects defined within a given database become numerous and complex. Occasionally, it would be useful if security could be easily copied to another database—the last thing anyone wants to do is re-create that stuff by hand!
Oh sure, you can use DTS to script it, after a fashion, but those DTS-generated scripts aren’t the most readable things—you’ll get almost nothing out of a quick glance. Plus, they’re very static, and will require editing unless all affected objects in the source database exist in the target as well. And finally, if you need to generate a dump or perform a copy programmatically, DTS leaves you high and dry.
To fill this need, I wrote the stored procedures that are the subject of this submission. One is a low-level utility procedure, not meant to be called directly by the user. The other is your entry point; I’ll cover it first.
sp_copydbsecurity
(
@SourceDB sysname=NULL,
@TargetDB sysname=NULL,
@mode varchar(10)=''
)
Note: It is named using the special prefix “sp_” on purpose, to make it callable from any database.
The first two parameters have default values because it is only necessary to specify one or the other. Both source and target can be explicitly passed if desired, but if either is omitted, the other is assumed to be the current default database.
The last parameter, @mode, dictates behavior: Passing the value ‘execute’ as @mode causes the procedure to perform a copy, thus altering the target database. If it is any other value, the procedure merely dumps its generated script without running it.
To be sure, sp_copydbsecurity is not terribly complicated, nor is it hard to read. Most of what it does involves creating temporary work tables to store the rows returned by a few system stored procedures (such as sp_helpuser and sp_helprolemember—hardly exotic) and low-level utility procedure. The rest of it uses those tables to emit single-field rows of T-SQL script.
One thing unique about the script it generates: Only objects that exist in the target are referenced.
See the next page for a complete listing of sp_copydbsecurity.