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

  • Hi Ken,

    Great scripts!

    Do you have anything that works with Windows Users\Logins?

    I wrote a script awhile ago that removes database access for each user and reassigns it.

    It's a pain if the user owns any objects as it has to swap ownership during the process,

    but it works. It also takes into account permissions.

    Your script is much more simple. The only problem is with Windows Users\Logins.

    Any ideas?

    Thanks!

  • Since the copy is not working from the article, I am posting it here. Maybe everyone will have better luck with this one.

    --Query 1

    DECLARE @Collation varchar(100)

    DECLARE @sql VARCHAR(2000)

    CREATE TABLE ##TempSync

    (

    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 ##TempSync

    EXEC sp_msforeachdb @sql

    SELECT * FROM ##TempSync

    DROP TABLE ##TempSync

    --Query 2

    DECLARE @Collation VARCHAR (100)

    DECLARE @sql VARCHAR(2000)

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

    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

  • I am getting the following error on SQL 2005 Sp2 on the query below

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

    Msg 137, Level 15, State 1, Line 1

    Must declare the scalar variable "@Collation".

  • DECLARE @Collation VARCHAR (100)

  • Nicely done, Ken... gonna add this one to my toolbelt... Heh, gotta find some way to get rid of the cursor, though (it's just me 😀 )

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • In fact, we can use sp_resolve_logins to solve this issue

  • 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 --

  • 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

  • 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?

  • 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

  • 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

  • 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

  • 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'

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply