MS SQL Database Table Prefix from Previous Owner
I have an MS SQL 2000 database that was backed up from a public server and restored at a test location for an upgrade test. The problem is that the user that had access permission on the public server does not exist on the testing server, and now all tables are prefixed with that username (which requires ALL queries against those tables to be changed!)
Is there any quick way to fix this? I have changed the database ow开发者_如何学Pythonner but this did not help.
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
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
精彩评论