Orphaned login report for all databases

  • SQLShark

    SSCrazy

    Points: 2196

    Comments posted to this topic are about the item Orphaned login report for all databases

  • Lynn Pettis

    SSC Guru

    Points: 442143

    I would make two changes.

    One, I would make is from using master..sysdatabases to sys.databases.

    Second, I would use the sys.server_principal and sys.database_principal tables to identify the orphaned logins. I wrote something just like that but the code is on secure networks back in Afghanistan so I can't readily show it without rewriting it. Maybe later.

    Other than that, not a bad script.

  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    Nice and neat. Very useful. Thanks for sharing. I did something similar while back that delivers a single resultset. It's under the "Analysis" section in this article[/url].

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

  • SQLShark

    SSCrazy

    Points: 2196

    You are right Lynn, I have to break a few of the old habits. Thanks for the comments, Ed

  • dawryn

    SSCarpal Tunnel

    Points: 4469

    I'd rather use (undocumented) stored procedures sp_MSForEachDB instead of cursor for single line solution:

    Exec sp_MSForEachDB 'EXEC [?].dbo.sp_change_users_login @Action=Report'

  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    dawryn (2/23/2016)


    I'd rather use (undocumented) stored procedures sp_MSForEachDB instead of cursor for single line solution:

    Exec sp_MSForEachDB 'EXEC [?].dbo.sp_change_users_login @Action=Report'

    Please don't use that procedure. Here is why: http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx

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

  • cstringham

    SSC Journeyman

    Points: 93

    Modified it to put the results into a temp table with an additional column DBName so I can see which databases the orphaned users belong to and joined it to sys.Server_Principals to see if there is a matching login by name, also uses sys.database_principals and sys.server_principals instead of the sproc to find which users aren't tied to logins.

    if object_id ('tempdb.dbo.#OrphanedUsers') IS NOT NULL

    DROP TABLE #OrphanedUsers

    create table #OrphanedUsers (DBName SYSNAME

    ,Username SYSNAME

    ,UserType CHAR(1)

    ,UserSID varbinary(85))

    set nocount on

    DECLARE @dbname varchar(255)

    DECLARE db_loop CURSOR

    READ_ONLY FOR

    Select [name] from sys.databases

    where [name] not in ('tempdb')

    OPEN db_loop

    FETCH NEXT FROM db_loop INTO @dbname

    WHILE (@@fetch_status <> -1)

    BEGIN

    INSERT INTO #OrphanedUsers(DBName, UserName, UserType, UserSid)

    EXEC ('USE [' + @DBNAME + ']; ' +

    'select db_name(), dp.Name, dp.Type, dp.SID ' +

    'from sys.database_principals dp ' +

    'LEFT JOIN sys.server_principals sp ' +

    'ON sp.sid = dp.sid ' +

    'where dp.principal_id > 4 and dp.type in (''S'', ''U'') and sp.sid is null and ' +

    'dp.name not in (''INFORMATION_SCHEMA'', ''guest'', ''NT AUTHORITY\NETWORK SERVICE'')')

    FETCH NEXT FROM db_loop INTO @dbname

    END

    CLOSE db_loop

    DEALLOCATE db_loop

    GO

    select DBName

    ,UserName

    ,UserType

    ,UserSID

    ,HasMatchingServerPrincipal =

    CASE WHEN sp.name IS NOT NULL THEN 1

    ELSE 0

    END

    ,CorrectionScript =

    CASE WHEN sp.name IS NOT NULL THEN 'Use [' + DBName + ']; Alter user [' + Username + '] WITH LOGIN = [' + sp.Name + '];'

    ELSE ''

    END

    from #OrphanedUsers ou

    left join sys.server_principals sp

    ON sp.name = ou.Username

    order by ou.DBName

    ,UserType

    ,UserName

    DROP TABLE #OrphanedUsers

  • stan.geiger

    SSChasing Mays

    Points: 647

    The only thing that scares me is that according to Microsoft, for the stored procedure:

    Maps an existing database user to a SQL Server login. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

  • Rudy Panigas

    SSChampion

    Points: 10695

    Hello,

    Cool script! I would just make one change and add the following

    PRINT @dbname

    EXEC ('EXEC [' + @dbname + '].dbo.sp_change_users_login @Action=Report')

    This would show the database name where the orphan user is located.

    Rudy

    Rudy

  • PHXHoward

    SSCrazy Eights

    Points: 8221

    That is a nice idea. I modified it a bit to make it more useful by excluding offline databases, combining the results, and adding the database name to the output.

    set nocount on

    DECLARE @Results table (DBName varchar(50), UserName varchar(50), UserSid varbinary(85))

    DECLARE @dbname varchar(255)

    DECLARE db_loop CURSOR

    READ_ONLY FOR

    Select [name] from master.sys.databases

    where [name] not in ('tempdb') and state = 0

    OPEN db_loop

    FETCH NEXT FROM db_loop INTO @dbname

    WHILE (@@fetch_status <> -1)

    BEGIN

    insert into @Results (Username, UserSid)

    EXEC ('EXEC [' + @dbname + '].dbo.sp_change_users_login @Action=Report')

    UPDATE @Results set DBName = @dbname where DBName is null

    FETCH NEXT FROM db_loop INTO @dbname

    END

    CLOSE db_loop

    DEALLOCATE db_loop

    select DBName, UserName, UserSid

    from @Results

  • SQLShark

    SSCrazy

    Points: 2196

    Nice job adding the additional code,

    Ed

  • Rudy Panigas

    SSChampion

    Points: 10695

    PHXHoward (2/24/2016)


    That is a nice idea. I modified it a bit to make it more useful by excluding offline databases, combining the results, and adding the database name to the output.

    set nocount on

    DECLARE @Results table (DBName varchar(50), UserName varchar(50), UserSid varbinary(85))

    DECLARE @dbname varchar(255)

    DECLARE db_loop CURSOR

    READ_ONLY FOR

    Select [name] from master.sys.databases

    where [name] not in ('tempdb') and state = 0

    OPEN db_loop

    FETCH NEXT FROM db_loop INTO @dbname

    WHILE (@@fetch_status <> -1)

    BEGIN

    insert into @Results (Username, UserSid)

    EXEC ('EXEC [' + @dbname + '].dbo.sp_change_users_login @Action=Report')

    UPDATE @Results set DBName = @dbname where DBName is null

    FETCH NEXT FROM db_loop INTO @dbname

    END

    CLOSE db_loop

    DEALLOCATE db_loop

    select DBName, UserName, UserSid

    from @Results

    Nice job in this script and output is clean and simple.

    Rudy

    Rudy

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 13 posts - 1 through 13 (of 13 total)

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