exec sp_configure 'allow updates' , 1
reconfigure with override
go
ALTER 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 --
-- 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 = @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
GO
exec sp_configure 'allow updates' , 0
reconfigure with override
go
This page was last updated on May 01, 2006 04:28 PM.