Transfer of sids
-
Has anyone created a script to automaticallyt correct sids that have been orphaned?
It is straightforward to get the sids to a file that need correcting via:
exec master..xp_cmdshell 'osql -E -S<server> -d<db> -Q"EXEC sp_change_users_login ''report''" >> c:\temp\orphan.txt -s, -n -w50'
but for some reason you can't put this into a local table via tsql. Ideally I'd have the database name with the sids to be corrected so that I could correct it via the tsql:
EXEC sp_change_users_login 'Auto_Fix', '<username>', NULL, 'password'
for example if I paste the output/ data to excel I could use the concatenate command:
=CONCATENATE("EXEC sp_change_users_login 'Auto_Fix', ' ",A3,"', NULL,","'password'")
and this would correct everythng. Essentially I'm trying to get an automatic routine up to re-alias the sids on our offsite server.
Help and ideas appreciated!
Rob
-
Add this to the master db, remove it when you are done.
CREATE PROCEDURE SP_AUTOFIX_USERS
AS
/* USAGE FOR FIX USER SIDS FOR ALL DATABASES
SP_MSFOREACHDB "USE ?; EXEC SP_AUTOFIX_USERS;"
*/
-- Declare the variables to store the values returned by FETCH.
set nocount on
declare @login sysname
PRINT DB_NAME()
PRINT '--------'
DECLARE user_update_cursor CURSOR FOR
SELECT distinct name from sysusers where issqluser = 1 and name not in ('dbo', 'guest') order by name
OPEN user_update_cursor
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
FETCH NEXT FROM user_update_cursor
INTO @login
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
print @login
exec sp_change_users_login 'update_one', @login, @login
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM user_update_cursor
INTO @login
END
CLOSE user_update_cursor
DEALLOCATE user_update_cursor
-
That's smashing Nicholas, works a treat so it seems. There are a few errors whereby for some reason I have a few logins at the database level that do not exist on the master, do you know why that might occur?
Anyway it's a good script, so why would you say:
"remove it when you are done." ?
Bye for now,
Rob.
-
You don't have to remove it, however it's best to do so, so as you don't have stuff hanging out in the master db.
You might well not have created logins on the server that match those on your current server, so the login would not exist, however the user would within the database.
-
I hate cursors! Tested for MS SQL Server 2008 R2
SET NOCOUNT ON
declare @GetListOfOrphanUser table
(
rowid smallint IDENTITY(1,1),
UserName sysname,
UserSID varbinary(85)
)
declare @UserName sysname
, @NoOfUsers smallint
-- To generate the orphaned users list.
INSERT @GetListOfOrphanUser (UserName, UserSID)
EXEC sp_change_users_login 'report'
SET @NoOfUsers = @@ROWCOUNT
WHILE @NoOfUsers > 0
BEGIN
SELECT @UserName = UserName
FROM @GetListOfOrphanUser
WHERE rowid = @NoOfUsers
SET @NoOfUsers = @NoOfUsers - 1
BEGIN TRY
EXEC sp_change_users_login 'Update_One', @UserName, @UserName
/*
In development our SQL environment, orphaned user needs to be fixed
if and only if the corresponding login exists.
*/
END TRY
BEGIN CATCH
/*
Nothing to do incase the logins for equivalent users does not exist.
Over here, it can customised to remove the orphaned user
in case equivalent login does not exist
*/
END CATCH
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply