Go homepage(回首页)
Upload pictures (上传图片)
Write articles (发文字帖)

The author:(作者)归海一刀
published in(发表于) 2014/2/3 6:34:22
Mssql批量修改权限_[SQL Server教程]

Mssql批量修改权限_[SQL Server教程]

今天恢复一个在别人机器备份的数据库到SQL SERVER，恢复后该数据库中有一个mooip用户，该用户是数据库所有者，很多表的所有者也是他，但安全性里的登录中没有该用户，删除不了该用户，提示“因为选定的用户拥有对象，所以无法除去该用户”！
具体解决方式如下：
1 如果是一张表有此问题，解决方式如下：

exec sp_changeobjectowner 'mooip.tablename', 'dbo'

这样就把tablename指定的表的所有者赋值给了dbo用户

2 如果所有表都存在此问题，这是就要编写存储过程实现批量修改了

IF OBJECT_ID('ChangeAll') IS NOT NULL Drop PROC ChangeAll
GO


Create PROCEDURE ChangeAll (
@old sysname,
@new sysname
)
AS
DECLARE @objname sysname


SET NOCOUNT ON


IF USER_ID(@old) IS NULL
BEGIN
RAISERROR ('The @old passed does not exist in the database', 16, 1)
RETURN
END
IF USER_ID(@new) IS NULL
BEGIN
RAISERROR ('The @new passed does not exist in the database', 16, 1)
RETURN
END


DECLARE owner_cursor CURSOR FOR


Select name FROM sysobjects Where uid = USER_ID(@old)
OPEN owner_cursor
FETCH NEXT FROM owner_cursor INTO @objname
WHILE (@@fetch_status <> -1)
BEGIN
SET @objname = @old + '.' + @objname
EXEC sp_changeobjectowner @objname, @new
FETCH NEXT FROM owner_cursor INTO @objname
END


CLOSE owner_cursor
DEALLOCATE owner_cursorz
GO


EXEC ChangeAll @old = 'mooip', @new = 'dbo'


这样就完成了批量修改权限的工作


来源:网络






赞

If you have any requirements, please contact webmaster。(如果有什么要求，请联系站长)




QQ:154298438
QQ:417480759