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.

CREATE procedure sp_copydbsecurity
   (
      @SourceDB sysname=NULL,
      @TargetDB sysname=NULL,
      @mode varchar(10)=''
   )
AS

SET NOCOUNT ON

if (@SourceDB is null) AND (@TargetDB is null)
begin
   print 'You must specify either @SourceDB or @TargetDB'
   return 0
end

if (@SourceDB is null)
   Set @SourceDB = DB_NAME()
if (@TargetDB is null)
   Set @TargetDB = DB_NAME()

if (@SourceDB = @TargetDB)
begin
   print '@SourceDB and  @TargetDB cannot be the same (you must pass
      DEFAULT for any omitted parameter)'
   return 0
end



declare @db sysname
declare @server sysname
declare @sql varchar(8000)
set @db = DB_NAME()
set @server = convert(sysname, SERVERPROPERTY('servername'))


Create Table #scriptbuf (
   script varchar(7000) null,
   [id] int IDENTITY(1,1) not NULL,
   [sort] int not NULL
)

Set @sql = 'USE ' + @TargetDB + '
'
INSERT INTO #scriptbuf (script, sort) values(@sql, 0)



Create Table #sourceuserlist (
    UserName        sysname        collate database_default Null
   ,GroupName       sysname        collate database_default Null
   ,LoginName       sysname        collate database_default Null
   ,DefDBName       sysname        collate database_default Null
   ,UID             smallint       Null
   ,SID             varbinary(85)  Null
)

Set @Sql = 'USE ' + @SourceDB + '
insert #sourceuserlist execute sp_helpuser
'
execute(@Sql)


Create Table #targetuserlist
   (
    UserName        sysname        collate database_default Null
   ,GroupName       sysname        collate database_default Null
   ,LoginName       sysname        collate database_default Null
   ,DefDBName       sysname        collate database_default Null
   ,UID             smallint       Null
   ,SID             varbinary(85)  Null
)

Set @Sql = 'USE ' + @TargetDB + '
insert #targetuserlist execute sp_helpuser
'
execute(@Sql)



CREATE TABLE #sourceperms (
   [priv] sysname not null,
   [schema] sysname not null,
   [obj] sysname not null,
   [user] sysname not null,
   [AccessGrant] varchar(30) not null,
   [action]  int not null,
   [protecttype]  int not null,
   [sysstat]  int not null,
   [id] int not null
)

Set @Sql = 'USE ' + @SourceDB + '
insert #sourceperms execute sp_resolveperms;2
'
execute(@Sql)


CREATE TABLE #targetperms (
   [priv] sysname not null,
   [schema] sysname not null,
   [obj] sysname not null,
   [user] sysname not null,
   [AccessGrant] varchar(30) not null,
   [action]  int not null,
   [protecttype]  int not null,
   [sysstat]  int not null,
   [id] int not null
)
Set @Sql = 'USE ' + @TargetDB + '
insert #targetperms execute sp_resolveperms;2
'
execute(@Sql)


CREATE TABLE #sourceroles (
   [RoleName] sysname not null,
   [RoleId] int not null,
   [IsAppRole] int not null
)

Set @Sql = 'USE ' + @SourceDB + '
insert #sourceroles execute sp_helprole
'
execute(@Sql)


CREATE TABLE #targetroles (
   [RoleName] sysname not null,
   [RoleId] int not null,
   [IsAppRole] int not null
)

Set @Sql = 'USE ' + @TargetDB + '
insert #targetroles execute sp_helprole
'
execute(@Sql)


CREATE TABLE #sourcerolemembers (
   [DBRole] sysname not null,
   [MemberName] sysname not null,
   [MemberSID] sysname not null
)


-- for reasons unknown, sp_helprolemember does not return
-- members of user-defined db roles, even though the SQL
-- statement below was copied directly from it

Set @Sql = 'USE ' + @SourceDB + '
insert #sourcerolemembers 
select DbRole = g.name, MemberName = u.name, MemberSID = u.sid
                from sysusers u, sysusers g, sysmembers m
                where   g.uid = m.groupuid
                   and g.issqlrole = 1
                   and u.uid = m.memberuid
                order by 1, 2
'


execute(@Sql)

CREATE TABLE #targetrolemembers (
   [DBRole] sysname not null,
   [MemberName] sysname not null,
   [MemberSID] sysname not null
)

Set @Sql = 'USE ' + @TargetDB + '
insert #targetrolemembers 
select DbRole = g.name, MemberName = u.name, MemberSID = u.sid
                from sysusers u, sysusers g, sysmembers m
                where   g.uid = m.groupuid
                   and g.issqlrole = 1
                   and u.uid = m.memberuid
                order by 1, 2
'
-- execute sp_helprolemember

execute(@Sql)


INSERT INTO #scriptbuf (script, sort) 
SELECT  'if not exists (select * from master.dbo.syslogins where
   loginname = N''' + UserName + ''') 
   exec sp_addlogin N''' + UserName + '''
', 1
FROM #sourceuserlist WHERE UserName NOT LIKE '%\%'


INSERT INTO #scriptbuf (script, sort) 
SELECT 'if not exists (select * from master.dbo.syslogins where
   loginname = N''' + UserName + ''') 
   exec sp_grantlogin N''' + UserName + '''
', 2
FROM #sourceuserlist WHERE UserName LIKE '%\%'


INSERT INTO #scriptbuf (script, sort) 
SELECT 'exec sp_grantdbaccess N''' + UserName + ''' , N''' +
   UserName + '''
', 3
FROM #sourceuserlist WHERE UserName NOT IN (SELECT UserName FROM
   #targetuserlist)


INSERT INTO #scriptbuf (script, sort) 
SELECT 'exec sp_addrole N''' + s.RoleName + '''
', 4
 FROM #sourceroles s LEFT OUTER JOIN #targetroles t
	ON s.RoleName = t.RoleName 
	WHERE t.RoleName is null



INSERT INTO #scriptbuf (script, sort) 
SELECT 'exec sp_addrolemember ''' + s.dbrole + ''', ''' +
   s.membername + '''
', 5
   FROM #sourcerolemembers s LEFT OUTER JOIN #targetrolemembers t
   ON s.dbrole = t.dbrole and s.membername = t.membername
   WHERE t.dbrole is null


INSERT INTO #scriptbuf (script, sort) 
SELECT  'if exists (select * from sysobjects where [name] = N''' +
   [obj] + ''')
   GRANT ' + [priv] + ' ON [' + [schema] + '].[' + [obj] + ']
   TO [' + [user] + ']
', 6
FROM #sourceperms WHERE (obj NOT LIKE 'dt_%') AND (obj NOT LIKE 'sys%')


if @mode <> 'execute'
begin
   print '-- generating script only, not executing'
   select top 100 percent script FROM (select distinct script, sort
      from #scriptbuf) drs order by [sort]
end
else
begin
   select top 100 percent script FROM (select distinct script, sort
      from #scriptbuf) drs order by [sort]
   set @sql = 'select top 100 percent script FROM (select distinct
      script, sort from #scriptbuf) drs order by [sort]'
   exec master..xp_execresultset @sql, @Targetdb
end



DROP TABLE #scriptbuf
DROP TABLE #sourceuserlist
DROP TABLE #targetuserlist
DROP TABLE #sourceperms
DROP TABLE #targetperms
DROP TABLE #sourceroles
DROP TABLE #targetroles
DROP TABLE #sourcerolemembers
DROP TABLE #targetrolemembers

The utility procedure focuses on extracting permissions from system tables, and turning all those undocumented magic numbers into the T-SQL keywords they represent.

There are actually two parts to sp_resolveperms. The first part generates a rowset designed for human readability. All possible permissions for any given object are represented in a single row. The second part, (sp_resolveperms;2) is called by sp_copydbsecurity, and is designed to return data that facilitates script generation.

I haven't tried either under SQL Server 2005 yet, but suffice to say, if any of it still works there, chalk it up to blind luck and nothing more.

Note: All magic numbers and their meanings (according to me) are brought to you courtesy of good-old-fashioned trial-and-error, observation, and assumptions based thereupon. Although they are believed to be correct, no claims of accuracy or fitness of use for any purpose are to be implied...

The reality is that the only thing really special about this code (other than its obvious utility value) is the amount of elbow grease and reverse engineering it represents. I hope you find it useful.

See the next page for a complete listing of sp_resolveperms.

CREATE PROCEDURE  sp_resolveperms AS

-- Programmer: Mark J. McGinty
-- Purpose: Resolve/list object-level permissions
-- Date: 19 Oct 2004

SELECT [object name], [user or role], 
   CASE MAX([select])  WHEN 205 THEN 'allowed' WHEN 206 THEN
      'denied' ELSE '' END AS [select], 
   CASE MAX([insert])  WHEN 205 THEN 'allowed' WHEN 206 THEN
      'denied' ELSE '' END AS [insert],
   CASE MAX([update])  WHEN 205 THEN 'allowed' WHEN 206 THEN
      'denied' ELSE '' END AS [update],
   CASE MAX([delete])  WHEN 205 THEN 'allowed' WHEN 206 THEN
      'denied' ELSE '' END AS [delete],
   CASE MAX([execute]) WHEN 205 THEN 'allowed' WHEN 206 THEN
      'denied' ELSE '' END AS [execute],
   CASE MAX([DRI])     WHEN 205 THEN 'allowed' WHEN 206 THEN
      'denied' ELSE '' END AS [DRI]
FROM (
   SELECTTOP 100 PERCENT o.name AS [object name],
      user_name(o.uid) AS [schema],
      user_name(p.uid) AS [user or role],
      o.sysstat & 0xf AS [sysstat],
      p.id,
      CASE action WHEN 193 THEN 'select'
         WHEN 195 THEN 'insert'
         WHEN 196 THEN 'update'
         WHEN 197 THEN 'delete'
         WHEN 224 THEN 'execute'
         WHEN  26 THEN 'DRI'
         END AS PrivilegeDesc,

      CASE action WHEN 193 THEN protecttype ELSE 0 END AS [select],
      CASE action WHEN 195 THEN protecttype ELSE 0 END AS [insert],
      CASE action WHEN 196 THEN protecttype ELSE 0 END AS [update],
      CASE action WHEN 197 THEN protecttype ELSE 0 END AS [delete],
      CASE action WHEN 224 THEN protecttype ELSE 0 END AS [execute],
      CASE action WHEN  26 THEN protecttype ELSE 0 END AS [DRI],
      CASE protecttype WHEN 205 THEN 'allowed' 
         WHEN 206 THEN 'denied'
         ELSE 'unknown' END AS AccessGrant,
      action,
      protecttype
   FROM dbo.sysprotects p, dbo.sysobjects o
   WHERE o.id = p.id
      AND p.action IN (193, 195, 196, 197, 224, 26)
      --AND p.id = 437576597
   ORDER by 1, 2 
) drs GROUP BY [object name], [user or role]

GO


CREATE PROCEDURE  sp_resolveperms;2 AS


SELECT TOP 100 PERCENT
   CASE action WHEN 193 THEN 'SELECT'
               WHEN 195 THEN 'INSERT'
               WHEN 196 THEN 'UPDATE'
               WHEN 197 THEN 'DELETE'
               WHEN 224 THEN 'EXECUTE'
               WHEN  26 THEN 'REFERENCES'
   END AS priv,
   user_name(o.uid) AS [schema],
   o.name AS [obj],
   user_name(p.uid) AS [user],

   CASE protecttype WHEN 205 THEN 'allowed'
                    WHEN 206 THEN 'denied'
                    ELSE 'unknown' END AS AccessGrant,
   action,
   protecttype,
   o.sysstat & 0xf AS [sysstat],
   p.id
from dbo.sysprotects p, dbo.sysobjects o
where o.id = p.id
   AND protecttype = 205
   and p.action in (193, 195, 196, 197, 224, 26)
order by 1, 2