[Axapta-Knowledge-Village] Re: dbo to bmssa

Thu, 25 May 2006 02:05:12 -0700

You can use the SQL procedure below to change the object owner of
tables and views.
However, I would recommend changing the owner FROM bmmssa TO dbo.
Use this stored procedure to change the owner of the database:
sp_changedbowner 'bmssa'. Now, all new objects are created with dbo
as object owner. When moving the database between two servers you
should probably also use that stored procedure to re-map the login
user "bmssa" to the database user "dbo".

Regards,
Mikael


--------

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


--- In Axapta-Knowledge-Village@yahoogroups.com, "Steeve Gilbert"
<[EMAIL PROTECTED]> wrote:
>
> Hi group,
>
> 
>
>   I just want to know if there's something to cure us from the
modified
> objects that keep on changing owner from dbo to bmssa.  It's not
like
> its causing any real trouble in production, but sometimes I
restore live
> data in our test environment and sychronique, I end up with 2
tables
> with the same name under 2 owner different (dbo and bmssa).  If
you have
> any clue, please send them my way.
>
> 
>
> Thanks,
>
> Steeve...
>
> 
>
>
>
> [Non-text portions of this message have been removed]
>






Sharing the knowledge on Axapta.
Visit www.frappr.com/axapta for axapta friends.



SPONSORED LINKS
Business finance course Business to business finance Small business finance
Business finance consultant Business finance magazine Business finance schools

YAHOO! GROUPS LINKS



Reply via email to