Steeve...
-----Message d'origine-----
De : Axapta-Knowledge-Village@yahoogroups.com [mailto:[EMAIL PROTECTED] De la part de Mikael Sorensen
Envoyé : 25 mai 2006 05:05
À : Axapta-Knowledge-Village@yahoogroups.com
Objet : [Axapta-Knowledge-Village] Re: dbo to bmssa
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.
Yahoo! Groups Links
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
- Visit your group "Axapta-Knowledge-Village" on the web.
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
