Error executing sp_change_users_login

Hello -
Hello, i'm trying to run
Exec sp_change_Users_login 'report'
From a few databases on my servers. When I try to execute,=20
i'm receiving the following error message:
=AB Server: Msg 15289, Level 16, State 1, Procedure=20
sp_change_users_login, Line 27
Terminating this procedure. Cannot have an open=20
transaction when this is
run.=BB
Here's the code i'm trying to execute:
=AB=20
if exists (select * from dbo.sysobjects where id =3D=20
object_id(N'[dbo].[OutputOrphanUsers]') and OBJECTPROPERTY
(id, N'IsUserTable') =3D 1)
drop table [dbo].[OutputOrphanUsers]
go
create table OutputOrphanUsers=20
(uname varchar(30),
usid varbinary(85))
go
insert OutputOrphanUsers EXEC=20
sp_change_users_login 'Report'=BB
I hope that you can help me, i cant found any workarround=20
to this situation.
Best regerdsI'm not sure there is a workaround... I tried hiding the query in openrowset
and that didn't work either... ie
insert OutputOrphanUsers select a.* FROM
OPENROWSET('SQLOLEDB','servername';'logi
n';'password',
'exec sp_change_users_login report') AS a
Actually what you could do is get the source... in master sp_helptest
sp_change_users_login and change the code that requires no transaction (but
only if the parameter is report. Then re-add it as a different name. The
common naming convention would be sp__change_users_login (2 underscores
after sp).
That will work.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:153ed01c4157e$e99d76d0$a101280a@.phx
.gbl...
Hello -
Hello, i'm trying to run
Exec sp_change_Users_login 'report'
From a few databases on my servers. When I try to execute,
i'm receiving the following error message:
Server: Msg 15289, Level 16, State 1, Procedure
sp_change_users_login, Line 27
Terminating this procedure. Cannot have an open
transaction when this is
run.
Here's the code i'm trying to execute:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[OutputOrphanUsers]') and OBJECTPROPERTY
(id, N'IsUserTable') = 1)
drop table [dbo].[OutputOrphanUsers]
go
create table OutputOrphanUsers
(uname varchar(30),
usid varbinary(85))
go
insert OutputOrphanUsers EXEC
sp_change_users_login 'Report'
I hope that you can help me, i cant found any workarround
to this situation.
Best regerds