Syncing logins 1

[YL5956]

I've restored a production user database to my test server but the login information is out of sync between the restored user database and the master database on my test server. I was told to run sp_change_users_login store procedure in the user database after recreating all the logins manually. I'm getting an ODBC/authorization error when I try to run the store procedure.

Is there a better way to sync the login information and how do I resolve the ODBC/authorization error?

How do I implement single user mode on the master database? Thanks.

 

[tlbroadbent]

Check this article on SWYNK.com.

Fixing broken logins and transferring passwords

http://www.swynk.com/friends/boyle/fixingbrokenlogins.asp

Terry Broadbent


"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[YL5956]

I tried to the steps but I kept on getting an odbc/autorization error. How can I run this stored procedure successfully? Thanks

 

[tlbroadbent]

How are you trying to run the SP - Query Analyzer or another program? How are you logging on to SQL Server? Does the login have System Administrator role? Terry Broadbent


"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[YL5956]

I'm logging in locally using the SA or admin account. I'm running using query analyzer. A question away from the main top. How would I implement single user mode for the master database? Thanks.

 

[dbtech]

To use SQL server in Single user mode start the server from command prompt.

sqlservr -m

check BOL for more options on sqlservr



dbtech

 

[tlbroadbent]

I assume you are trying to run sp_change_users_login from Query Analyzer as SA. It should work. The syntax would be similar to the following.

/* Report the orphaned users */
USE MyDB
go
sp_change_users_login 'Report'
Go

/* Fix one orphaned user */
USE MyDB
go
EXEC sp_change_users_login 'Update_One', 'UserName', 'LoginName'
Go

If it is not working, please post the complete error message along with your SQL Script for executing the SP.

Thanks,

Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[YL5956]

Here is the stored procedure I'm using.

CREATE PROCEDURE sp_change_users_login
@Action varchar(10) -- REPORT / UPDATE_ONE / AUTO_FIX
,@UserNamePattern sysname = Null
,@LoginName sysname = Null
AS
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @exec_stmt nvarchar(430)

declare @ret int,
@FixMode char(5),
@cfixesupdate int, -- count of fixes by update
@cfixesaddlogin int, -- count of fixes by sp_addlogin
@dbname sysname,
@loginsid varbinary(85),
@110name sysname

-- SET INITIAL VALUES --
select @dbname = db_name(),
@cfixesupdate = 0,
@cfixesaddlogin = 0

-- ERROR IF IN USER TRANSACTION --
if @@trancount > 0
begin
raiserror(15289,-1,-1)
return (1)
end

-- INVALIDATE USE OF SPECIAL LOGIN/USER NAMES --
if @LoginName = 'sa'
begin
raiserror(15287,-1,-1,@LoginName)
return (1)
end
if lower(@UserNamePattern) in ('dbo','guest','INFORMATION_SCHEMA')
begin
raiserror(15287,-1,-1,@UserNamePattern)
return (1)
end

-- 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

-- HANDLE UPDATE_ONE --
if upper(@Action) = 'UPDATE_ONE'
begin

-- CHECK PERMISSIONS --
if not is_member('db_owner') = 1
begin
raiserror(15288,-1,-1,'SA or DBO',@Action)
return (1)
end

-- ERROR IF PARAMS NULL --
if @UserNamePattern IS Null or @LoginName IS Null
begin
raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
return (1)
end

-- VALIDATE PARAMS --
-- Can ONLY remap SQL Users to SQL Logins! Should be no need
-- for re-mapping NT logins, and if you try, you'll mess up
-- the user status bits! (see samsmith)
if not exists (select name from sysusers where
name = @UserNamePattern -- match user name
and issqluser = 1) -- must be sql user
begin
raiserror(15291,-1,-1,'User',@UserNamePattern)
return (1)
end
select @loginsid = sid from master.dbo.syslogins where
loginname = @LoginName -- match login name
and isntname = 0 -- cannot use nt logins
if @loginsid is null
begin
raiserror(15291,-1,-1,'Login',@LoginName)
return (1)
end

-- ERROR IF SID ALREADY IN USE IN DATABASE --
if exists (select sid from sysusers where sid = @loginsid
and name <> @UserNamePattern)
begin
raiserror(15063,-1,-1)
return (1)
end

-- CHANGE THE USERS LOGIN (SID) --
update sysusers set sid = @loginsid, updatedate = getdate()
where name = @UserNamePattern and issqluser = 1
and sid <> @loginsid

-- FINALIZATION: REPORT AND EXIT --
if @@error <> 0 or @@rowcount <> 1
raiserror(15295,-1,-1, 0)
else
raiserror(15295,-1,-1, 1)
return (0)
end

-- ERROR IF NOT AUTO_FIX --
if upper(@Action) <> 'AUTO_FIX'
begin
raiserror(15286,-1,-1,@Action)
return (1)
end

-- HANDLE AUTO_FIX --
-- CHECK PERMISSIONS --
if not is_srvrolemember('sysadmin') = 1
begin
raiserror(15288,-1,-1,'SA or DBO',@Action)
return (1)
end

-- VALIDATE PARAMS --
if @UserNamePattern IS Null or @LoginName IS NOT Null
begin
raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
return (1)
end

-- LOOP THRU ORPHANED USERS --
select @exec_stmt = 'DECLARE Cursor110_Users cursor for
select name from sysusers
where name = N' + quotename( @UserNamePattern , '''')+ '
and issqluser = 1 and suser_sname(sid) is null'
EXECUTE (@exec_stmt)
OPEN Cursor110_Users

WHILE (110=110)
begin
FETCH next from Cursor110_Users into @110name
if (@@fetch_status <> 0)
begin
DEALLOCATE Cursor110_Users
BREAK
end

-- IS NAME ALREADY IN USE? --
-- if suser_sid(@110name) is null
if not exists(select * from master.dbo.syslogins where loginname = @110name)
begin

-- ADD LOGIN --
execute @ret = sp_addlogin @110name, Null, @dbname
if @ret <> 0 or suser_sid(@110name) is null
begin
raiserror(15497,16,1,@110name)
deallocate Cursor110_Users
return (1)
end
select @FixMode = '1AddL'
raiserror(15293,-1,-1,@110name)
end
ELSE
begin
Select @FixMode = '2UpdU'
Raiserror(15292,-1,-1,@110name)
end

-- REPORT ERROR & CONTINUE IF DUPLICATE SID IN DB --
select @loginsid = suser_sid(@110name)
if user_sid(@loginsid) is not null
begin
raiserror(15331,-1,-1,@110name,@loginsid)
CONTINUE
end

-- UPDATE SYSUSERS ROW --
update sysusers set sid = @loginsid, updatedate = getdate() where name = @110name
if @@error <> 0
begin
raiserror(15498,17,127)
deallocate Cursor110_Users
return (1)
end


if @FixMode = '1AddL'
Select @cfixesaddlogin = @cfixesaddlogin + 1
else
Select @cfixesupdate = @cfixesupdate + 1
end -- loop 110

-- REPORT AND RETURN SUCCESS --
raiserror(15295,-1,-1,@cfixesupdate)
raiserror(15294,-1,-1,@cfixesaddlogin)
return (0) -- sp_change_users_login



Here is the error message

[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

Thanks for all your help.

 

[tlbroadbent]

Could you post the SQL code that you are using to call sp_change_users_login? Terry Broadbent

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[YL5956]

All I'm doing is using query analyzer and executing the sp.

Execute {sp_change_users_login} am I not doing this correctly? Thanks again for all your help.

 

[tlbroadbent]

The syntax is incorrect. I posted the correct syntax previously.

I have a SQL script that will fix all logins in the database. You can use this if you want instead of trying to figure out how to use sp_change_users_login.

Copy the following SQL script to the clipboard and paste it into Query Analyzer. Switch to the database with user/login problems and run the script.

/* suppress counts from being displayed */
set nocount on

/* declare variables */
declare @username sysname
declare @usersid varbinary(85)
declare @cmd varchar(255)

/* declare a cursor that will contain a list of user names and user sids */
select @cmd = &quot;use &quot; + db_name() + &quot; declare upd_cursor cursor
for 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&quot;

exec (@cmd)

/* open the cursor */
open upd_cursor

/* get the first row from the cursor */
fetch next from upd_cursor into @username, @usersid

/* loop through the rows in the cursor */
while @@fetch_status = 0
begin
select @cmd = &quot;exec sp_change_users_login auto_fix, &quot; + @username
exec (@cmd)
/* get next user name and user sid */
fetch next from upd_cursor into @username, @usersid
end

/* close the cursor */
deallocate upd_cursor Terry Broadbent

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[YL5956]

How would I sync the passwords between the restored database and the freshly created user? Thanks.