迁移用户以及用户对应的角色
发布时间：2020-12-12 14:32:48 所属栏目：MsSql教程 来源：网络整理
导读：?? 我们经常在迁移数据库时，需要将登录账号，用户和用户对应的角色也迁移过去，以下脚本就是迁移SQLServer数据库用户以及用户对应 的角色的脚本；将在Message里面生成对应的脚本。 SET NOCOUNT ON GO if exists ( SELECT * FROM tempdb.dbo.sysobjects WHER
|
?? 我们经常在迁移数据库时，需要将登录账号，用户和用户对应的角色也迁移过去，以下脚本就是迁移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
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
