locked
Pull list of User mappings for each login on each Instance RRS feed

  • Question

  •  

     

    Hi Team,

    Some analysis is going in all instances..

    can you  please guide on this i want

    pull a list of user mappings for each login on each instance

    how can we do i just struck here only...

     

    Tx

     

     

     

     

     

     


    subu
    Tuesday, August 2, 2011 1:20 PM

Answers

  • Hi Subu,

    You may be looking for a script to return all the server roles for logins and as well as database roles for the database users. I have a script written for performing security audit on server. You can find the script at http://manu-sqlserverblog.blogspot.com/2011/08/sql-server-security-audit.html

    Hope this helps.
    Thanks, Manu Please mark this as answered if this answers your question

    • Proposed as answer by Peja Tao Thursday, August 4, 2011 2:48 AM
    • Marked as answer by Peja Tao Tuesday, August 9, 2011 7:05 AM
    Tuesday, August 2, 2011 11:10 PM

All replies

  • select dp.name as users,sp.name as logins from sys.database_principals dp
    join  sys.server_principals sp
    on dp.sid=sp.sid

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, August 2, 2011 1:23 PM
  • Hi URi,

     

    i have around 250 users is there and  login and user configuration are the same i will get only few(7) i want all the mappins.

    and i have to configure server and db roles can you please guide me

    Really appriciate those guys.

     

    Tx

     


    subu
    Tuesday, August 2, 2011 1:44 PM
  • As Uri's example shows, the database principals in each database are the maped users. But the database principal can be a server role or a Windows group. So there might not be a user mapping for each person who can connect.

    This brings up the question of "What are you trying to do?" I suspect you want to figure out who can access each database. But here's the problem:

    Take 3 Windows users; Tom, Dick, and Harry. All are in a Windows group called Triad. Make a SQL Server login for the Triad Windows group. Map the Triad login as a user in database D1 and D2. Check sys.database_principals in D1 and D2 and you will see one mapped user (for these three people). Now Tom and Dick login to SQL Server and connect to D1 and D2. In D1, Tom creates a table. Now sys.database_principals will add Tom as a user so that Tom can own the table. Now you have 2 users in D1 (for these 3 guys) and 1 user in D2 (for these 3 guys). Harry has never connected to SQL Server. SQL Server doesn't even know Harry exists and is a member of the Triad group. But he can connect tomorrow if he wants.

    So to understand who can connect and to which databases, you must understand who the mapped users represent, who the SQL Server roles represent, who the logins represent, and all the Windows group memberships, and then add all that together.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Tuesday, August 2, 2011 3:37 PM
  • Hi Subu,

    You may be looking for a script to return all the server roles for logins and as well as database roles for the database users. I have a script written for performing security audit on server. You can find the script at http://manu-sqlserverblog.blogspot.com/2011/08/sql-server-security-audit.html

    Hope this helps.
    Thanks, Manu Please mark this as answered if this answers your question

    • Proposed as answer by Peja Tao Thursday, August 4, 2011 2:48 AM
    • Marked as answer by Peja Tao Tuesday, August 9, 2011 7:05 AM
    Tuesday, August 2, 2011 11:10 PM
  • In addition 

    To server roles you can join on principal_id not on sid


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, August 3, 2011 7:15 AM
  • Think you are looking for a cleaned up out put from this:

    exec master..sp_MSloginmappings

    Monday, February 20, 2012 5:20 AM
  • And cleaned up it becomes: (although a merge with the code out of http://manu-sqlserverblog.blogspot.com/2011/08/sql-server-security-audit.html would be nice)

    create table #tempww

    (LoginName nvarchar(max),

    DBname nvarchar(max),

    Username nvarchar(max),

    AliasName nvarchar(max))

    insert into #tempww

    exec master..sp_msloginmappings

    Select * from #tempww order by dbname, username

    drop table #tempww

    Monday, February 20, 2012 5:46 AM