September 30, 2002 at 10:56 am
Hello all, I have a user who has left the company. I want to delete his account on the server, but he has some objects that are owned by him. How do I change the ownership of those objects (to dbo)? Thanks.
September 30, 2002 at 10:59 am
Take a look at the sp_changeobjectowner stored procedure.
You can easily write a query that retrieves a list of objects owned by the user (sysobjects table is perfect for this) and then executes an sp_changeobjectowner for each one.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
September 30, 2002 at 11:35 am
Suggest you to use sp_depends to find out
the dependencies before change the objects
owner.
September 30, 2002 at 11:47 am
You'll want to be careful of the results brought back by sp_depends. The system stored procedure sp_depends queries the sysdepends system tables to find dependencies. However, there are cases where sysdepends doesn't get updated properly. Consider the following:
CREATE PROC usp_IAmDependent
AS
EXEC usp_Independent
GO
and
CREATE PROC usp_Independent
AS
SELECT @@VERSION
GO
When you create usp_IAmDependent, you'll receive the following error:
quote:
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'usp_Independent.' The stored procedure will still be created.
After creating usp_Independent, if you run sp_depends on it you'll get back:
quote:
Object does not reference any object, and no objects reference it.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply