迁移用户以及用户对应的角色

概述   我们经常在迁移数据库时，需要将登录账号，用户和用户对应的角色也迁移过去，以下脚本就是迁移SQLServer数据库用户以及用户对应 的角色的脚本；将在Message里面生成对应的脚本。 SET NOCOUNT ON GO if

   我们经常在迁移数据库时，需要将登录账号，用户和用户对应的角色也迁移过去，以下脚本就是迁移sqlServer数据库用户以及用户对应

的角色的脚本；将在Message里面生成对应的脚本。

              SET         NOCOUNT         ON                 GO                 if                 exists        (         SELECT                 *                 FROM         tempdb.dbo.sysobjects         WHERE         xtype        =        '        U        '                 and         name        =        '        userrole        '         )         begin                 drop                 table         tempdb.dbo.userrole         CREATE                 table         tempdb.dbo.userrole ( servername         varchar        (        50        ),dbname         varchar        (        100        ),username         varchar        (        100        ),category         varchar        (        100        ),rolename         varchar        (        100        ),publicrole         varchar        (        200        ) )         end                 else                 begin                 CREATE                 table         tempdb.dbo.userrole ( servername         varchar        (        50        ),publicrole         varchar        (        200        ) )         end                 go                 EXEC         master.dbo.sp_MSforeachdb         '        INSERT INTO tempdb.dbo.userrole SELECT @@servername,        ''        ?        ''        ,b.name AS Username,CASE WHEN b.isntgroup=1 THEN         ''        ntgroup        ''         WHEN b.isntuser=1 THEN         ''        ntuser        ''         WHEN b.issqluser=1 THEN         ''        sqluser        ''         WHEN b.isaliased=1 THEN         ''        aliased        ''         WHEN b.issqlrole=1 THEN         ''        sqlrole        ''         WHEN b.isapprole=1 THEN         ''        approle        ''         END AS category,c.name AS Rolename,(CASE WHEN EXISTS ( SELECT 1 FROM ?.dbo.sysusers WHERE name=b.name ) THEN         ''        CREATE USER [        ''        +b.name+        ''        ] FOR LOGIN [        ''        +b.name+        ''        ]         ''         ELSE         ''''         END ) as publicrole from ?.dbo.sysmembers a join ?.dbo.sysusers b on a.memberuID=b.uID join ?.dbo.sysusers c on a.groupuID=c.uID where a.memberuID<>1 --and (a.memberuID<16384 or a.memberuID>16393)        '                 if                 exists        (         SELECT                 *                 FROM         tempdb.dbo.sysobjects         WHERE         xtype        =        '        U        '                 and         name        =        '        RoleTmep        '         )         drop                 table                 temp        .dbo.RoleTmep         SELECT         dbname,username,rolename,publicrole         into         #RoleTmep         FROM         (         SELECT                 *                 FROM         tempdb.dbo.userrole         WHERE         username         IN         (         SELECT         name         FROM         sys.server_principals         WHERE         is_Disabled        =        0                 AND         type         IN        (        '        S        '        ,        '        U        '         ) )         --        AND rolename <>'RSExecRole'                 union                 select                 @@SERVERname        ,        '        db        '        ,name,        '        sqluser        '        ,        '        sysadmin        '        ,        ''                 FROM         sys.syslogins         WHERE         sysadmin        =        1                 AND         isntgroup        =        0                 AND         name         IN         (         SELECT         name         FROM         sys.server_principals         WHERE         is_Disabled        =        0         ) ) a         order                 by         username         SELECT         dbname,rolename         from         #RoleTmep         DECLARE                 @dbname                 varchar        (        50        ),        @username                 varchar        (        50        ),        @rolename                 varchar        (        50        ),        @publicrole                 varchar        (        200        ),        @count                 int                 DECLARE         cur_role         CURSOR         LOCAL STATIC READ_ONLY FORWARD_ONLY         FOR                 SELECT         dbname,publicrole         from         #RoleTmep         set                 @count        =        0                 open         cur_role         fetch                 next                 from         cur_role         into                 @dbname        ,        @username        ,        @rolename        ,        @publicrole                 while                 @@fetch_status                 =                 0                 begin                 if        (        len        (        @publicrole        )        >        5        )         begin                 print        (        '        --------Add User:        '        +        @username        +        '         On:        '        +        @dbname        +        '        -----------------        '        )         print        (        '        USE         '        +        @dbname        )         print        (        '        GO        '        )         print        (        '        IF NOT EXISTS (SELECT 1 FROM         '        +        @dbname        +        '        .dbo.sysusers WHERE name=N        '''        +        @username        +        '''         )         '        )         print        (        @publicrole        )         print        (        '        ----------------------------------------------------------------------        '        )         print        (        '        GO        '        )         end                 if        (        @rolename        =        '        sysadmin        '        )         begin                 print        (        '        --------Add User:        '        +        @username        +        '         Role:Sysadmin        '        +        '        -----------------        '        )         print        (        '        EXEC master..sp_addsrvrolemember @loginame = N        '''        +        @username        +        '''        ,@rolename = N        ''        sysadmin        '''        )         print        (        '        ----------------------------------------------------------------------        '        )         print        (        '        GO        '        )         end                 else                 begin                 print        (        '        ---------Add User:        '        +        @username        +        '        Role:        '        +        @rolename        +        '         On         '        +        @dbname        +        '        ----------        '        )         print        (        '        USE         '        +        @dbname        )         print        (        '        GO        '        )         print        (        '        EXEC sp_addrolemember N        '''        +        @rolename        +        '''        ,N        '''        +        @username        +        ''''        )         print        (        '        ----------------------------------------------------------------------        '        )         print        (        '        GO        '        )         end                 fetch                 next                 from         cur_role         into                 @dbname        ,        @publicrole                 set                 @count        =        @count        +        1                 end                 close         cur_role         deallocate         cur_role         GO                 truncate                 table         #RoleTmep         truncate                 table         tempdb.dbo.userrole         GO                 drop                 table         #RoleTmep         drop                 table         tempdb.dbo.userrole       

总结

以上是内存溢出为你收集整理的迁移用户以及用户对应的角色全部内容，希望文章能够帮你解决迁移用户以及用户对应的角色所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。