一般开发,SQL Server的数据库所有者为dbo.但是为了安全,有时候可能把它换成其它的名称.
所有者变换不是很方便.这里列出两种供参考
第一种:
--
手动修改系统表
-- 设备可以手动修改表
exec sp_configure ' allow updates ' , 1
RECONFIGURE WITH OVERRIDE
-- 更改对象所有者
update sysobjects set uid = 1 where uid = 5
update sysobjects set uid = 5 where uid = 1 AND xtype != ' S '
update sysobjects set uid = 1 where name = ' syssegments '
update sysobjects set uid = 1 where name = ' sysconstraints '
exec sp_configure ' allow updates ' , 0
RECONFIGURE WITH OVERRIDE
第二种:
-- 设备可以手动修改表
exec sp_configure ' allow updates ' , 1
RECONFIGURE WITH OVERRIDE
-- 更改对象所有者
update sysobjects set uid = 1 where uid = 5
update sysobjects set uid = 5 where uid = 1 AND xtype != ' S '
update sysobjects set uid = 1 where name = ' syssegments '
update sysobjects set uid = 1 where name = ' sysconstraints '
exec sp_configure ' allow updates ' , 0
RECONFIGURE WITH OVERRIDE
--
-******************更改权限以添加HHRC用户*************************
-- step1 添加第三方用户,如"chn"
-- step2 运行sql,加对象权限移至第三方用户
-- step3 添加hhrc用户
-- step4 运行sql,将对象权限移至hhrc用户
declare @tblname varchar ( 30 )
declare @tblown varchar ( 50 )
declare tbl_cur cursor for
select name from sysobjects where uid = ' 5 ' and status >= 0 and xtype in ( ' U ' , ' P ' )
-- select * from sysusers
open tbl_cur
fetch next from tbl_cur into @tblname
while @@fetch_status = 0
begin
set @tblown = ' web56433. ' + @tblname
EXEC sp_changeobjectowner @tblown , ' chn '
print @tblname
fetch next from tbl_cur into @tblname
end
close tbl_cur
deallocate tbl_cur
-- step1 添加第三方用户,如"chn"
-- step2 运行sql,加对象权限移至第三方用户
-- step3 添加hhrc用户
-- step4 运行sql,将对象权限移至hhrc用户
declare @tblname varchar ( 30 )
declare @tblown varchar ( 50 )
declare tbl_cur cursor for
select name from sysobjects where uid = ' 5 ' and status >= 0 and xtype in ( ' U ' , ' P ' )
-- select * from sysusers
open tbl_cur
fetch next from tbl_cur into @tblname
while @@fetch_status = 0
begin
set @tblown = ' web56433. ' + @tblname
EXEC sp_changeobjectowner @tblown , ' chn '
print @tblname
fetch next from tbl_cur into @tblname
end
close tbl_cur
deallocate tbl_cur
如何解决孤立用户问题
在您向目标服务器传输登录帐户和密码后,您的用户可能还无法访问数据库。登录帐户与用户是靠安全识别符 (SID) 关联在一起的;在您移动数据库后,如果 SID 不一致,SQL Server 可能会拒绝用户访问数据库。此问题称为孤立用户。如果您使用 SQL Server 2000 DTS 传输登录功能来传输登录帐户和密码,就可能会产生孤立用户。此外,被允许访问与源服务器处于不同域中的目标服务器的集成登录帐户,也会导致出现孤立用户。
1. | 查找孤立用户。在目标服务器上打开查询分析器,然后在您移动的用户数据库中运行以下代码: |
exec
sp_change_users_login
'
Report
'
2. | 如果数据库所有者 (dbo) 被当作孤立用户列出了,请在用户数据库中运行下面的代码: |
exec
sp_changedbowner
'
sa
'