Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Sync All Logins on a Server in a single click using SP_MSForEachDB Expand / Collapse
Author
Message
Posted Tuesday, June 3, 2008 6:48 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 6:01 PM
Points: 1,856, Visits: 307
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
Post #511059
Posted Tuesday, June 3, 2008 11:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 7, 2014 11:32 PM
Points: 7, Visits: 119
In fact, we can use sp_resolve_logins to solve this issue
Post #511132
Posted Wednesday, June 4, 2008 8:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 10:33 AM
Points: 1,359, Visits: 380
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 --
Post #511412
Posted Wednesday, June 4, 2008 9:22 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, July 2, 2012 2:29 PM
Points: 633, Visits: 256
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
Post #511474
Posted Thursday, June 5, 2008 7:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 12:18 PM
Points: 1, Visits: 70
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?
Post #512098
Posted Thursday, June 5, 2008 7:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 6:38 PM
Points: 6, 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
Post #512105
Posted Thursday, June 5, 2008 7:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 2, 2012 7:05 AM
Points: 75, 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



Post #512117
Posted Thursday, June 5, 2008 4:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 7, 2014 11:23 AM
Points: 2, Visits: 15
Hi Ken,
You saved my time :) this scripts works like a charm in my test environment (bringing production databases to staging).

It works like a charm!

Thanks a lot!
-DB
Post #512568
Posted Wednesday, March 7, 2012 7:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 7, 2012 7:53 PM
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'
Post #1263394
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse