SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sync All Logins on a Server in a single click using SP_MSForEachDB


Sync All Logins on a Server in a single click using SP_MSForEachDB

Author
Message
Mushroom
Mushroom
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1981 Visits: 332
Thanks Ken

That script is very helpful. But I think that if you want to run the first script before and after running second why not have it as a combined script like the following.

DECLARE @Collation varchar(100)DECLARE @SQL VARCHAR(2000)

CREATE TABLE ##PreSync(DB_NME Varchar(50),DBUserName varchar(50),SysLoginName varchar(50))

CREATE TABLE ##AfterSync(DB_NME Varchar(50),DBUserName varchar(50),SysLoginName varchar(50))

SELECT @Collation = CONVERT(SYSNAME,DatabasePropertyEx('master','Collation'))

SET @SQL = 'USE [?]
SELECT ''?'' DB_NME,
A.name DBUserName,
B.loginname SysLoginName
FROM sysusers A
JOIN master.dbo.syslogins B
ON A.name Collate ' + @Collation + ' = B.Name
JOIN master.dbo.sysdatabases C
ON C.Name = ''?'' WHERE issqluser = 1
AND (A.sid IS NOT NULL
AND A.sid <> 0x0)
AND suser_sname(A.sid) IS NULL
AND (C.status & 32) =0 --loading
AND (C.status & 64) =0 --pre recovery
AND (C.status & 128) =0 --recovering
AND (C.status & 256) =0 --not recovered
AND (C.status & 512) =0 --offline
AND (C.status & 1024) =0 --read only
ORDER BY A.name'

INSERT into ##PreSync
EXEC sp_msforeachdb @SQL

IF Exists(SELECT * FROM ##PreSync)
BEGIN
SET @SQL = 'USE [?]
DECLARE @DBUserName varchar(50)
DECLARE @SysLoginName varchar(50)
DECLARE SyncDBLogins CURSOR FOR
SELECT A.name DBUserName,
B.loginname SysLoginName
FROM sysusers A
JOIN master.dbo.syslogins B
ON A.name Collate ' + @Collation + ' = B.Name
JOIN master.dbo.sysdatabases C
ON C.Name = ''?'' WHERE issqluser = 1
AND (A.sid IS NOT NULL
AND A.sid <> 0x0)
AND suser_sname(A.sid) IS NULL
AND (C.status & 32) =0 --Loading
AND (C.status & 64) =0 --pre recovery
AND (C.status & 128) =0 --recovering
AND (C.status & 256) =0 --not recovered
AND (C.status & 512) =0 --offline
AND (C.status & 1024) =0 --read only
ORDER BY A.name
OPEN SyncDBLogins
FETCH NEXT FROM SyncDBLogins
INTO @DBUserName, @SysLoginName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login ''update_one'', @DBUserName, @SysLoginName
FETCH NEXT FROM SyncDBLogins
INTO @DBUserName, @SysLoginName
END
CLOSE SyncDBLogins
DEALLOCATE SyncDBLogins'

EXEC sp_msforeachdb @SQL

SET @SQL = 'USE [?]
SELECT ''?'' DB_NME,
A.name DBUserName,
B.loginname SysLoginName
FROM sysusers A
JOIN master.dbo.syslogins B
ON A.name Collate ' + @Collation + ' = B.Name
JOIN master.dbo.sysdatabases C
ON C.Name = ''?'' WHERE issqluser = 1
AND (A.sid IS NOT NULL
AND A.sid <> 0x0)
AND suser_sname(A.sid) IS NULL
AND (C.status & 32) =0 --loading
AND (C.status & 64) =0 --pre recovery
AND (C.status & 128) =0 --recovering
AND (C.status & 256) =0 --not recovered
AND (C.status & 512) =0 --offline
AND (C.status & 1024) =0 --read only
ORDER BY A.name'

INSERT into ##AfterSync
EXEC sp_msforeachdb @SQL
END

SELECT * FROM ##PreSync
SELECT * FROM ##AfterSync

DROP TABLE ##PreSync
DROP TABLE ##AfterSync
wyfccc
wyfccc
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 147
In fact, we can use sp_resolve_logins to solve this issue
Mark D Powell
Mark D Powell
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2137 Visits: 466
You would have to run sp_resolve_logins for each and every database which would mean you would have to dump the sys.logins table for each and every database. That is likely to be a lot more work for the DBA than just syncing up the existing entries between the database and the instance.

If you only have a couple of users to fix you could use sp_change_users_log to re-sync.

-- Mark D Powell --
Eichpeel
Eichpeel
Say Hey Kid
Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)

Group: General Forum Members
Points: 695 Visits: 257
Ken, Thank you so much. I tired to reformat the script many times and it would keep giving me syntax errors. Thanks again.

"Any fool can write code that a computer can understand. Good programmers write
code that humans can understand." -Martin Fowler et al, Refactoring: Improving the Design of Existing Code, 1999
brobert1
brobert1
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 75
I am getting the same thing on 2000
server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'Collate'.

Did you get a fix?
steeled
steeled
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 88
I have created a txt file with the script properly formatted. If you want it, send me a PM. You can just rename it to .sql and open it in Management Studio
JStiney
JStiney
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 446
Ken,
It was interesting to see how to reestablish users on a different instance of SQL Server by changing the SID.
I’ve wondered for a long time if this was as good, or maybe better, than the way we do it.
We perform the task every time we do a restore from production to test or development or build a new server. We run restores from production to test or development most every day.
We use a stored procedure that first saves the user’s security groups in a temporary table.
It then revokes the user’s access to the database.
Next it adds the user back to the database, and lastly back to the database Roles.
It works for both SQL Server and NT logins. We do have a standard that all security is granted through database Roles, so there is no code dealing with users who own database objects.
Since we are just using sp_revokedbaccess, sp_grantdbaccess, and sp_addrolemember and we rebuild all users, we don’t have to worry about SIDs.
Thanks for the interesting script.
JohnS



Denise Fuchs Beadle
Denise Fuchs Beadle
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 22
Hi Ken,
You saved my time Smile this scripts works like a charm in my test environment (bringing production databases to staging).

It works like a charm!

Thanks a lot!
-DB
brian.miller.au
brian.miller.au
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Ken,

Been a while since I worked with MSSQL, but just joined as I hope my next job takes me there again. Anyway I used this in 2003 and maybe 2005...

Apologies if I have blown all credibility on my 1st post.
Also Not sure if the @Action variable should be in single quotes or not.

-- ------------------------------------------------
-- Use this procedure to link the security account for a user in the current database with a different login or the same named login or the original login particularly when the link is lost after a database restore linking the user to the new login without losing the user's permissions. login cannot be sa, and user cannot be the dbo, guest, or INFORMATION_SCHEMA users.
-- ----------------------------------------------
--SYNTAX
--sp_change_users_login [ @Action = ] 'action'
-- [ , [ @UserNamePattern = ] 'user' ]
-- [ , [ @LoginName = ] 'login' ]
-- ----------------------------------------------
-- 'action' = Report
-- Lists the users, and their security identifiers (SID), that are in the database, not linked to any login.
EXEC sp_change_users_login @Action = 'Report'
-- -------------------------------------------------
-- 'action' = Auto_Fix
-- Links user entries in the sysusers table in the current database to logins of the same name in syslogins.
EXEC sp_change_users_login @Action = 'Auto_Fix'
-- ---------------------------------------------------
-- 'action' = [Update_One]
-- Links the specified user in the database to login. login must already exist. user and login must be specified.
sp_change_users_login @Action = Update_One , @UserNamePattern = 'user', @LoginName = 'login'
-- ---------------------------------------------------
-- IE If the login exists and is not linked just use it in the syntax
-- so if you want to connect old login 'Mary' with old user 'Mary' try this
EXEC sp_change_users_login @Action = Update_One, 'Mary', 'Mary'
--
-- IE: Change the login for an existing user
-- This example changes the link between user Mary in the pubs database and the existing login,
-- STEP 1: Add the new login NewMary
USE master
go
EXEC sp_addlogin 'NewMary'
go
--
-- STEP 2: Change the user account to link with the 'NewMary' login.
USE pubs
go
EXEC sp_change_users_login @Action = Update_One, 'Mary', 'NewMary'
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search