July 30, 2007 at 1:29 am
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]
degrem_m
Degremont
July 30, 2007 at 2:23 am
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
July 30, 2007 at 5:01 am
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.
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