October 14, 2007 at 8:12 pm
Gud day im using sp_change_users_login 'report' command to display all the orphaned users in each databases and insert the result in a created table but it gives me an error when inserting
"Terminating this procedure. Cannot have an open transaction when this is run."
From what i understood when running the stored procedure, QA cannot permit an open transaction (INSERT) to run while it is gathering information on orphaned users. My question is.... Are there any possible solution so that i can include/insert the results of the stored procedure in a table??? thanks in advance...
BTW here is the script i made in case you want to know
IF EXISTS (SELECT name from sysobjects where name = 'tabletest')
DROP TABLE tabletest
CREATE TABLE tabletest (DBName varchar(500), UserName varchar(250), UserSID int)
DECLARE @db varchar(500)
DECLARE @set varchar(1024)
DECLARE db CURSOR FOR
SELECT name FROM master..sysdatabases
OPEN db
FETCH NEXT FROM db INTO @db
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @set = @db+'..sp_change_users_login '+'''Report'''
INSERT INTO tabletest exec (@set)
FETCH NEXT FROM db INTO @db
END
CLOSE db
DEALLOCATE db
"-=Still Learning=-"
Lester Policarpio
October 14, 2007 at 9:18 pm
Anyways i got my answer in this link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
That SELECT statement will display orphaned users. I modified the select statement and came with an idea so that I can generate a script that will delete orphaned users...
If you want to view the script here it is...
/***********************************
Script Made by: Lester A. Policarpio
Any Suggestions and Clarifications feel free
to email me at lpolicarpio2005@yahoo.com
This script will delete orphaned users in all
databases of a certain server
***********************************/
SET NOCOUNT ON
IF EXISTS (SELECT name from sysobjects where name = 'orphaned')
DROP TABLE orphaned
CREATE TABLE orphaned (DBName varchar(500), UserName varchar(250))
DECLARE @db varchar(500)
DECLARE @set varchar(1024)
DECLARE db CURSOR FOR
SELECT name FROM master..sysdatabases WHERE name NOT IN
('master','model','msdb','tempdb','DBA','pubs','Northwind')
OPEN db
FETCH NEXT FROM db INTO @db
WHILE (@@FETCH_STATUS = 0)
BEGIN
--SELECT statement below is from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
SET @set = 'SELECT UserName = name,'+''''+@db+''''+' as '+'''DBName'''+' FROM '+@db+'..sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name'
INSERT INTO orphaned(UserName,DBName) exec (@set)
FETCH NEXT FROM db INTO @db
END
CLOSE db
DEALLOCATE db
--Second CURSOR
DECLARE @db2 varchar(500)
DECLARE @db3 varchar(500)
DECLARE @change_login varchar(50)
DECLARE db2 CURSOR FOR
SELECT DBName,UserName FROM orphaned
OPEN db2
FETCH NEXT FROM db2 INTO @db2,@db3
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @change_login = @db2+'..sp_revokedbaccess '+''''+@db3+''''
print (@change_login) -- lets you view first the orphaned users
--exec (@change_login) --execute this part and it will delete the orphaned users
FETCH NEXT FROM db2 INTO @db2,@db3
END
CLOSE db2
DEALLOCATE db2
DROP TABLE orphaned
SET NOCOUNT OFF
"-=Still Learning=-"
Lester Policarpio
October 14, 2007 at 9:53 pm
That script is not actually originated from that forum.
It's from the text of system procedure sp_change_users_login:
[font="Courier New"] -- HANDLE REPORT --
if upper(@Action) = 'REPORT'
begin
-- VALIDATE PARAMS --
if @UserNamePattern IS NOT Null or @LoginName IS NOT Null
begin
raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
return (1)
end
-- GENERATE REPORT --
select UserName = name, UserSID = sid from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name
return (0)
end[/font]
Your error came from here:
[font="Courier New"] -- ERROR IF IN USER TRANSACTION --
if @@trancount > 0
begin
raiserror(15289,-1,-1)
return (1)
end
[/font]
You can see it for yourself if you open this SP for editing.
_____________
Code for TallyGenerator
October 14, 2007 at 11:19 pm
AHhh ok thanks for the clarification it just happened that i saw the statement in that link that is why i included it in my script. Anyways thanks for the clarification i think i can remove the URL in my script.
"-=Still Learning=-"
Lester Policarpio
October 15, 2007 at 2:26 am
Lester Policarpio (10/14/2007)
i think i can remove the URL in my script.
And you can look for hints inside other system procedures.
Wisdom comes from there.
:Wow:
_____________
Code for TallyGenerator
October 15, 2007 at 2:48 am
Yeah noted 😎
"-=Still Learning=-"
Lester Policarpio
October 18, 2007 at 9:47 am
Are you regularly getting orphaned users? The main reason I know of for this happening is restoring a database from a different server. I'm not so sure I'd be quick to dropped orphaned users on a regular basis without finding why they are orphaned first.
Steve
October 18, 2007 at 6:58 pm
Yeah thats the reason why im having lots of orphaned users but anyways i made a script that will delete orphaned users, recreate server and database logins/roles
"-=Still Learning=-"
Lester Policarpio
October 19, 2007 at 7:31 am
Are you using sp_help_revlogin? That will script out all the logins for you and transfer the correct sid to the new machine.
http://support.microsoft.com/kb/246133/
Also, the whole subject of "How to move databases between computers that are running SQL Server" is addressed here including "How to resolve orphaned users":
http://support.microsoft.com/kb/314546/
Steve
October 19, 2007 at 7:54 am
Also, I've been working on disaster recovery and am looking at repopulating a different machine from my backups. I do not want to restore to the master database since the machine is a different name. I am nightly running sp_help_revlogin to disk to capture the current logins (the disk is backed up nightly). When I take it to the new machine, I comment out the local machine logins and then run it to create the logins. I wll then restore the backups of the other desired databases.
If I reuse the machine and don't want to reinstall Sql-Server, toying with a script that drops all the logins on that are new machine that are not sysadmin. This is still a work in progress. Here's what I have so far.
select 'exec sp_droplogin "' + rtrim(name) + '";'
from master.dbo.syslogins
where sysadmin = 0 and isntname = 0
go
select 'exec sp_revokelogin "' + rtrim(name) + '";'
from master.dbo.syslogins
where sysadmin = 0 and isntname = 1
go
I know that if I do a disaster recovery test, the logins will be in use the various databases as users. I am considering whether to drop (or detach) those databases or script out dropping the users from the databases.
Steve
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply