Post reply

User without mapping

  • March 24, 2009 at 2:11 am

    #202185

    Hi,

    is there a possibility (e.g. a stored procedure) to find out which user do not have any mapping to a database. We have a lot of users an it should work with a script.

    Thanks

  • March 24, 2009 at 2:32 am

    #965015

    Use sp_change_users_login 'Report'

    You will get the list of orphan users in that database.

  • March 24, 2009 at 3:07 am

    #965026

    Hi,

    that´s not my problem. I have a login, an the login has no databases mapped. So it´s not an orphaned, and the "sp_change_users_login 'Report' " reports nothing.

  • March 30, 2009 at 1:44 am

    #968159

    I just wrote a script; tested in 2008 so should work in 2005 also.

    Ref: http://sqllearnings.blogspot.com/2009/03/get-listing-of-databases-user-has.html

    I think that's what you are looking for?

    Thanks.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • March 30, 2009 at 2:10 am

    #968167

    Here is something that I wrote a while ago. Notice that I only checked if the user belongs to the sysadmin server role, but you’ll might want to check for other server level roles. Also if some of your user’s database use the user guest, then those logins might be working with those databases.

    create table #sids (sid varbinary(85))

    exec sp_msforeachdb 'insert into #sids(sid) select sid from [?].dbo.sysusers where sid is not null'

    select sl.name

    from master.dbo.syslogins sl left join #sids s on sl.sid = s.sid

    where s.sid is null and IS_SRVROLEMEMBER ('sysadmin',name) = 0

    drop table #sids

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • March 30, 2009 at 2:43 am

    #968174

    Hi,

    Thank´s a lot - the script work´s well on SQL 2005. But it does not list users which have no user mapping.

    The reason for the solution I need is, that the users where managed in MS Dynamics Navision. In Nav it´s possible to delete the User from the database without delete the Login from the SQL Server, so a lot of users exists without a mapping.

    Greetings

    Werner

  • March 30, 2009 at 3:10 am

    #968182

    Please use the correct terms, so we’ll all understand what you need. Do you want to find a login that has no mapping to user in any user’s database? If this is the case, then this script does just that (notice that it will not show you users that are part of sysadmin server role that have no mapping to any database. Beside that it will show logins with no user mapping in any database). If you need something else, please explain what you need.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • March 31, 2009 at 12:54 pm

    #969363

    I think you want orphaned logins. I've attached a sscript for 2005 for this. It exevn generates a script to drop them !

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Viewing 8 posts - 1 through 7 (of 7 total)

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