September 26, 2007 at 5:34 am
Yesterday my supervisor showed me how to restore a database from one server (the test server) to another (production) server.
The problem was that at the end I had to go ahead and manually add all the users (about 12) to the newly restored database on the production server.
He isnt sure why we lose the information, and I would like to know if ther is a workaround (there must be!)
I have read a little about some strategies to go ahead and do this (sp_change_users_login), but I wanted to ask you fellas also.
Thanks for your time.
DJ
September 26, 2007 at 6:03 am
longtime known issue.
logins on a server are stored in the master database. access to the login is stored in the actual database (select permissions only, db_datareader for example) are inside the database you resotred.
if you restore a database on a DIFFERENT server, the login doesn't exist. It wouldn't be right security wise that if you restored a database, it instantly gave logins access to a different server that were not explicitly assigned.
How would you know that my user "Bob" on my domain "Sneaky" is the same "Bob" on your domain? you can infer it based on the name, so if you have a "Bob", you can use the stored proc to fix the login.
I typically delete all users from a restored database form other servers, then assign the logins that do exist on my server to have access.
if the same names do exist, the sp_change_user_logins is the way to fix it;
here's a proc that does it for all users in a particular database:
use master
go
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_change_users_logins Script
Date: 2001-10-30 11:51:54 AM ******/
CREATE PROCEDURE sp_change_users_logins
@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 --
-- declare the cursor explicity global so that the cursor
-- exists at the end of the exec stmt regardless of the database
-- option value for 'default to local cursor'
select @exec_stmt = 'DECLARE Cursor110_Users cursor global for
select name from sysusers
where name Like 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
Like @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 Like @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
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/* finally recreate the users*/
Lowell
September 26, 2007 at 11:57 am
It's called "orphaned users".
To find:
EXEC sp_change_users_login 'Report'
To fix:
EXEC sp_change_users_login 'Auto_Fix', 'Chris', NULL, 'pw'
September 26, 2007 at 12:34 pm
Keep in mind that sp_change_users_login ONLYworks for SQL Server Logins
* Noel
September 27, 2007 at 8:41 am
Try using a free tool to move your SQL logons and permissions next time. It's called SQLpermissions. It will create a T-SQL script that you can use to automatically move SQL logons AND permissions from one server to another. You can download it at: http://www.idera.com/freetools
September 27, 2007 at 9:31 am
There's also a stored procedure you can use to script logins from the old server that you can then run on the new server. It preserves SIDs and passwords so you shouldn't have to clean up orphaned DB users on the destination server. It's not perfect, but it's useful if you need to move logins for which you don't want to change (and don't know) the passwords.
Google "sp_help_revlogin"; IIRC there's an MS KB article that explains everything.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply