当前位置：文江博客话题详情

来自前任所有者的 MS SQL 数据库表前缀

发布于 2024-08-22 08:52:04 字数 159 浏览 4 评论 0原文

我有一个从公共服务器备份并在测试位置恢复以进行升级测试的 MS SQL 2000 数据库。问题是在公共服务器上具有访问权限的用户在测试服务器上不存在，现在所有表都以该用户名为前缀（这需要更改针对这些表的所有查询！）

有没有快速的方法解决这个问题吗？我已经更改了数据库所有者，但这没有帮助。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

遇到 2024-08-29 08:52:04

创建登录名和用户，但从 sysusers 中找出 SID

EXEC sp_addlogin 'TheLogin', 'ThePassword', @sid = ???
EXEC sp_adduser 'TheLogin','TheUser'

注意：SQL Server 2000 所以不能使用 CREATE LOGIN 或 CREATE USER

Create the login and users, but find out the SID from sysusers

EXEC sp_addlogin 'TheLogin', 'ThePassword', @sid = ???
EXEC sp_adduser 'TheLogin','TheUser'

Note: SQL Server 2000 so can't use CREATE LOGIN or CREATE USER

回复收藏 0 原文

提笔落墨 2024-08-29 08:52:04

好的，找到了答案 - 对象所有者必须更改为 DBO，无需在 SQL 脚本/查询中添加对对象的引用前缀 - 本例中的对象是数据库表

这是一个将更改的脚本数据库中对象的所有者（不是我自己的代码）

DECLARE @currentObject nvarchar(517)
DECLARE @qualifiedObject nvarchar(517)
DECLARE @currentOwner varchar(50)
DECLARE @newOwner varchar(50)

SET @currentOwner = 'old_owner'
SET @newOwner = 'dbo'

DECLARE alterOwnerCursor CURSOR FOR
SELECT [name] FROM dbo.sysobjects 
WHERE xtype = 'U' or xtype = 'P'
AND LEFT([name], 2) <> 'dt'
OPEN alterOwnerCursor
FETCH NEXT FROM alterOwnerCursor INTO @currentObject
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @qualifiedObject = CAST(@currentOwner as varchar) + '.' + CAST(@currentObject as varchar)
   EXEC sp_changeobjectowner @qualifiedObject, @newOwner
   FETCH NEXT FROM alterOwnerCursor INTO @currentObject
END
CLOSE alterOwnerCursor
DEALLOCATE alterOwnerCursor

Ok, found the answer - the OBJECT owner must be changed to DBO, negating the need to prefix references to your object in your SQL scripts/queries - the object in this case being the database table(s)

Here is a script that will change the owner for objects within a database (not my own code)

DECLARE @currentObject nvarchar(517)
DECLARE @qualifiedObject nvarchar(517)
DECLARE @currentOwner varchar(50)
DECLARE @newOwner varchar(50)

SET @currentOwner = 'old_owner'
SET @newOwner = 'dbo'

DECLARE alterOwnerCursor CURSOR FOR
SELECT [name] FROM dbo.sysobjects 
WHERE xtype = 'U' or xtype = 'P'
AND LEFT([name], 2) <> 'dt'
OPEN alterOwnerCursor
FETCH NEXT FROM alterOwnerCursor INTO @currentObject
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @qualifiedObject = CAST(@currentOwner as varchar) + '.' + CAST(@currentObject as varchar)
   EXEC sp_changeobjectowner @qualifiedObject, @newOwner
   FETCH NEXT FROM alterOwnerCursor INTO @currentObject
END
CLOSE alterOwnerCursor
DEALLOCATE alterOwnerCursor

回复收藏 0 原文

~没有更多了~