Orphan users fpr all user databases

  • About a year after this thread I wrote an article on the topic. It targeted 2005 and 2008 but the technique works on 2012 as well. If you read the comments in the discussion Wayne talks about a column that was added in SQL 2012 that makes it a bit simpler still.

    http://www.sqlservercentral.com/articles/Security/98202/[/url]

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Find below script will help for fix the orphan users for all DB's.

    Fix Orphan users in all databases

    SET NOCOUNT ON

    BEGIN

    declare @UserName sysname

    declare @NoOfUsers smallint

    declare @dbname varchar(50)

    declare @query varchar(2000)

    declare @query_1 varchar(2000)

    CREATE TABLE #orphanusers

    (

    rowid smallint IDENTITY(1,1),

    UserName sysname,

    UserSID varbinary(85)

    )

    CREATE TABLE #databases

    (

    dbname varchar(50)

    )

    INSERT INTO #databases SELECT name from sys.databases where database_id > 4 and state_desc = 'ONLINE'

    WHILE EXISTS(SELECT 1 FROM #databases)

    BEGIN

    SET @query = ''

    select TOP 1 @dbname = dbname from #databases order by dbname

    SET @query = 'EXEC ' + @dbname + '.dbo.sp_change_users_login ''report'''

    -- To generate the orphaned users list.

    INSERT INTO #orphanusers EXEC(@Query)

    WHILE EXISTS(SELECT 1 FROM #orphanusers)

    BEGIN

    SELECT TOP 1 @UserName = UserName

    FROM #orphanusers

    order by rowid

    BEGIN TRY

    SET @query_1 = 'EXEC ' + @dbname + '.dbo.sp_change_users_login ''Update_One'',' + @UserName+ ',' + @UserName

    Print 'Solving ' + @username + ' in ' +@dbname

    EXEC(@Query_1)

    END TRY

    BEGIN CATCH

    /* We can Probably drop the user if it is not associated with any login */

    PRINT 'No Existent Login for ' + @username

    END CATCH

    DELETE FROM #orphanusers where UserName = @UserName

    END

    DELETE FROM #databases where dbname = @dbname

    END

    DROP TABLE #orphanusers

    DROP TABLE #databases

    END

  • Find below script will help for fix the orphan users for all DB's.

    Fix Orphan users in all databases

    SET NOCOUNT ON

    BEGIN

    declare @UserName sysname

    declare @NoOfUsers smallint

    declare @dbname varchar(50)

    declare @query varchar(2000)

    declare @query_1 varchar(2000)

    CREATE TABLE #orphanusers

    (

    rowid smallint IDENTITY(1,1),

    UserName sysname,

    UserSID varbinary(85)

    )

    CREATE TABLE #databases

    (

    dbname varchar(50)

    )

    INSERT INTO #databases SELECT name from sys.databases where database_id > 4 and state_desc = 'ONLINE'

    WHILE EXISTS(SELECT 1 FROM #databases)

    BEGIN

    SET @query = ''

    select TOP 1 @dbname = dbname from #databases order by dbname

    SET @query = 'EXEC ' + @dbname + '.dbo.sp_change_users_login ''report'''

    -- To generate the orphaned users list.

    INSERT INTO #orphanusers EXEC(@Query)

    WHILE EXISTS(SELECT 1 FROM #orphanusers)

    BEGIN

    SELECT TOP 1 @UserName = UserName

    FROM #orphanusers

    order by rowid

    BEGIN TRY

    SET @query_1 = 'EXEC ' + @dbname + '.dbo.sp_change_users_login ''Update_One'',' + @UserName+ ',' + @UserName

    Print 'Solving ' + @username + ' in ' +@dbname

    EXEC(@Query_1)

    END TRY

    BEGIN CATCH

    /* We can Probably drop the user if it is not associated with any login */PRINT 'No Existent Login for ' + @username

    END CATCH

    DELETE FROM #orphanusers where UserName = @UserName

    END

    DELETE FROM #databases where dbname = @dbname

    END

    DROP TABLE #orphanusers

    DROP TABLE #databases

    END

Viewing 3 posts - 16 through 17 (of 17 total)

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