Changing Object Ownership
-
I have several tables, SPs, etc that are owned by a user account. I want to change these to be owned by DBO. How would I do that?
Thanks,
Chris
-
Look up sp_changedbowner first and then sp_revokedbaccess - BOL explains this and you can finetune this to suit your needs!
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks. BTW.. What does BOL mean? I'm a newbie...
-
Books online. Excellent source of information.
-
Excellent except when it's downright useless...here's the example I was looking for from BOL -
"Show first level dependency only" - BOL says it means...
"View only first-level dependencies for the selected object."
**ASCII stupid question, get a stupid ANSI !!!** -
I never said it was perfect
.BTW, how would you rephrase that??
-
I had a similar problem a while ago and came up with this for a solution. Probably not the correct way of doing it but it worked for me.
Just replace <username> with the user account name.
-- Do not report row count
SET NOCOUNT ON
-- Declare variable for table name
DECLARE @name sysname
CREATE TABLE #tmp
(NondboTables sysname,)
-- Insert only table names that have the RowUpdatedateTime field into the table.
INSERT #tmp(NondboTables)
SELECT [livedb].[dbo].[sysobjects].[name]
FROM [livedb].[dbo].[sysobjects]
WHERE [livedb].[dbo].[sysobjects].[name] LIKE '<username>%'
OR [livedb].[dbo].[sysobjects].[name] LIKE '<username>%'
-- Declare the cursor to loop through the table of table names
DECLARE TableName CURSOR
READ_ONLY
FOR
SELECT NondboTables
FROM #tmp
-- Open the cursor ready for use
OPEN TableName
/*
**
** Loop through the table, returning the table name. Use the variable @name
** to retrieve the latest record from the RowUpdateDDateTime field.
**
*/
FETCH NEXT FROM TableName INTO @name
WHILE (@@fetch_status <> -1) -- While NOT "FETCH statement failed or the row was beyond the result set".
BEGIN
IF (@@fetch_status <> -2) -- While NOT "Row fetched is missing".
BEGIN
EXEC('EXEC sp_changeobjectowner ' + @name + ', dbo' )
END
FETCH NEXT FROM TableName INTO @name
END
/*
**
** End Loop
**
*/
-- Close the cursor and deallocate the memory used.
CLOSE TableName
DEALLOCATE TableName
DROP TABLE #tmp
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy: -
I ran into a similar problem. I had an ASP.Net website I was developing using a local MSDE SQL Server 2000 instance, which used dbo. I went to upload to my web host, which gave me a database with access via a non-sa userid. The code didn't work until I changed or removed all the dbo references. I just removed them on references to table names in SQL statements, but they were required in referecences to stored procedures. So I came up with a routine that prefixed the owner name dynamically which it read from a config file.
Some stored procedures had references to udf's, which required the owner name prefixed. I had to edit these too.
-
You could use sp_changeobjectowner <object name>, <new object owner>. So it would be something like this:
sp_changeobjectowner 'Employees', 'sa'
-
yes, but then you have to remap dbo to sa or you still have the same problem to fix.
-
i use the following stored procedure I found ages agoexec chObjOwner 'test','dbo''output code to using sp_changeobjectowner-------------------------------------------if exists (select * from sysobjects where id = object_id(N'[dbo].[chObjOwner]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[chObjOwner]
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
CREATE proc chObjOwner( @usrName varchar(20), @newUsrName varchar(50))
as
-- @usrName is the current user
-- @newUsrName is the new user
set nocount on
declare @uid int -- UID of the user
declare @objName varchar(50) -- Object name owned by user
declare @currObjName varchar(50) -- Checks for existing object owned by new user
declare @outStr varchar(256) -- SQL command with 'sp_changeobjectowner'
set @uid = user_id(@usrName)
declare chObjOwnerCur cursor static
for
select name from sysobjects where uid = @uid
open chObjOwnerCur
if @@cursor_rows = 0
begin
print 'Error: No objects owned by ' + @usrName
close chObjOwnerCur
deallocate chObjOwnerCur
return 1
end
fetch next from chObjOwnerCur into @objName
while @@fetch_status = 0
begin
set @currObjName = @newUsrName + "." + @objName
if (object_id(@currObjName) > 0)
print 'WARNING *** ' + @currObjName + ' already exists ***'
set @outStr = "sp_changeobjectowner '" + @usrName + "." + @objName + "','" + @newUsrName + "'"
print @outStr
print 'go'
fetch next from chObjOwnerCur into @objName
end
close chObjOwnerCur
deallocate chObjOwnerCur
set nocount off
return 0
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
-
um i don't see why sp_changeobjectowner will not work. sp_changeobjectowner 'user.tablename', 'dbo'
Works for me
-
The chObjOwner mentioned above is very useful. It is from the msft knowledge base and builds a script that changes all objects for a given user instead of having to find and type each manually.
--From MSDN 3_23_03 (Microsoft Knowledge Base Article - 275312)
-- @usrName is the current user
-- @newUsrName is the new user
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply