Unable to change object owner sp_changeobjectowne after migration SQL 2000 to 2005

  • Hi all,

    I am in front of an issue that I didn't solve

    Issue description 

    We were unable to change Object Owner for multiple objects in your database. These objects include Views, stored procedures etc.

    Agreed Resolution

    to change object owner

     

    Work completed to date

    The query in KB 275312 only generates the code to change the object owners. We have to run the code to do the actual changes.

    INF: SQL Server Procedure to Change Object Owner : http://support.microsoft.com/kb/275312

     

    And when I tried sp_changeobjectowner, It didn’t work e.g. appendix 1.

     

     

    But it didn’t work

     

    I tried another way to solve this issue, but the behavior was very strange

     

     

    Step 1 : check the owner of object

     

     

     

    SELECT  name,USER_NAME(uid) AS [Owner],USER_ID(USER_NAME(uid)) AS [Owner] FROM  sysobjects WHERE name LIKE 'aspnet_Membership_SelectEmail'

     

    Result :  

    -   Name aspnet_Membership_SelectEmail                                                                                                   

    -   Owner winradio2

    -   Owner 7

     

     

    Step 2 : update table system : but it was impossible

     

     

     

    exec sp_configure 'allow',1

    go

    reconfigure with override

    go

    update sysusers set uid=USER_ID('dbo') where name LIKE 'aspnet_Membership_SelectEmail'

    go

    exec sp_configure 'allow',0

    go

    reconfigure with override

    GO

    Result :  

    Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.

    Msg 259, Level 16, State 1, Line 1

    Ad hoc updates to system catalogs are not allowed.

    Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.

     

    Step 3 : drop object and create object with the administror  Windows User in management studio. But the result was strange

     

     

     

    DROP PROCEDURE [admin].[aspnet_Membership_SelectEmail]

    GO

    CREATE PROCEDURE [admin].[aspnet_Membership_SelectEmail]

    BEGIN

    --... T-SQL code

    END

    GO

     

    SELECT  name,USER_NAME(uid) AS [Owner],USER_ID(USER_NAME(uid)) AS [Owner] FROM  sysobjects WHERE name LIKE 'aspnet_Membership_SelectEmail'

     

    Result :  

    -   Name aspnet_Membership_SelectEmail                                                                                                   

    -   Owner winradio2 à it was creazy, the owner still winradio2 and not my windows user or dbo

    -   Owner 7

     

    Workaround : create a new database and to do export data on it. But for our production, we didn't want this solution.

     

    Do you know, how can I do ?

     

    Appendix 1 : sp_changeobjectowner

     

    sp_changeobjectowner 'winradio2.aspnet_Membership_SelectEmail','dbo'

    go

    sp_changeobjectowner 'admin.aspnet_Membership_SelectEmail','dbo'

    go

    sp_changeobjectowner 'aspnet_Membership_SelectEmail','dbo'

    go

     

    Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 62

    Object 'winradio2.aspnet_Membership_SelectEmail' does not exist or is not a valid object for this operation.

    Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 75

    Object 'admin.aspnet_Membership_SelectEmail' does not exist or is not a valid object for this operation.

    Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 62

    Object 'aspnet_Membership_SelectEmail' does not exist or is not a valid object for this operation.

     

    Appendix 2

     

    The command ALTER SCHEMA schema_name TRANSFER object_name  doesn’t for the user. 

    The schema of this procedure is ADMIN but the owner is an hold user : winradio2

    I would like to change the owner winradio2 to dbo.

    à [admin].[aspnet_Membership_SelectEmail]


    Kindest Regards,

    degrem_m
    Degremont

  • In SQL Server 2005 the owner of a stored procedure, view, function ... is no longer a user. These objects are now organized into schemas (schemata). During an upgrade process form 2000 usually a schema is created for every user that owns an object. Unfortunately the name that is present in the definition of the stored procedure, etc is not necessarily its name/owner. The owner is stored separately from the object in system tables. To find out the real owner on 2005 for your object, run:

    SELECT s.name

    , o.name

    FROM sys.objects AS o

    JOIN sys.schemas AS s ON o.schema_id = s.schema_id

    WHERE o.name LIKE 'aspnet_Membership_SelectEmail'

    When you are trying to rename the object you need to refer to its name that is known to SQL Server. It may have happened that during your upgrade the system table version of the object is referring to a schema that does not have the same name as the old user owner. So once you have the schema owner that is returned by the above query, you should be able to change the owner of the object (the owner should be a schema, and not user). I'd suggest dropping the object (using the correct schema owner in the drop statement), and then creating it again. This will ensure that the object name in the definition (the textual representation) will match the name in the system tables.

    (see A reason to avoid sp_rename for more info.

    Also, on 2005 sysusers is not a system table, but a compatibility view, so you cannot just update it.

    Hope this helps,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Hi Andras,

    Many thanks, with you informations I could finish my script to grant stored procedure

     

     

    CREATE

     PROCEDURE [admin].[grantStoreProcedure] (@login varchar(50)) AS

    BEGIN

    declare @proc varchar(100)

    declare @owner varchar(100)

    declare @stmt nvarchar(200)

    --declare curse cursor for select name,USER_NAME(uid) AS [Owner] from sysobjects where type='P' ORDER BY [Owner] DESC

    declare curse cursor for SELECT o.name, s.name FROM sys.objects AS o JOIN sys.schemas AS s ON o.schema_id = s.schema_id WHERE s.name <> 'sys' and type ='P'

    OPEN CURSE

    fetch next from curse into @proc,@owner

    while @@fetch_status=0

    begin

    set @stmt='revoke execute on '+@owner+'.'+@proc+' to '+@login

    exec SP_EXECUTESQL @STMT

    print @stmt

    fetch next from curse into @proc,@owner

    end

    close curse

    deallocate curse

    declare CURSE2 cursor for SELECT o.name ,s.name FROM sys.objects AS o JOIN sys.schemas AS s ON o.schema_id = s.schema_id WHERE s.name <> 'sys' and type ='P'

    OPEN CURSE2

    fetch next from CURSE2 into @proc,@owner

    while @@fetch_status=0

    begin

    set @stmt='grant execute on '+@owner+'.'+@proc+' to '+@login

    exec SP_EXECUTESQL @STMT

    print @stmt

    fetch next from CURSE2 into @proc,@owner

    end

    close CURSE2

    deallocate CURSE2

    END

    GO

    Have a nice days.


    Kindest Regards,

    degrem_m
    Degremont

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply