System name of/for a login

  • Is it possible to find out the System name of/for a login? Eg: If I want to look for the system names who have the 'sa' role or 'xyz' role.Is there any system table that shows such details? Thanks!

  • Take a look at the view dbo.syslogins in the master database. This should give you what you need (at least for server roles - for database roles, look at dbo.sysusers table in whatever database)

    "Got no time for the jibba jabba!"
    -B.A. Baracus

  • syslogins is a quick check for the sysadmin column or some of the other roles.

    If you want a procedure, try sp_helpsrvrolemember (http://msdn.microsoft.com/en-us/library/aa933412(SQL.80).aspx)

  • I agree that syslogin helps in providing the key answers but not the system/host name that has a specific role attached to it.

  • Steve's advice tells you how to determine who is a member of a particular role. If you have servers connecting in directly, you'll see their logins in Domain\ComputerName$. If you list the role members, it should be easy to determine which ones are then computer accounts.

    K. Brian Kelley
    @kbriankelley

  • How about these commands? May them help.

    sp_helpuser

    sp_helprotect NULL, 'userName'--List permissions for specific user

    sp_helprotect @name = 'objName'--List user names for a specific object

    sp_helprotect NULL, NULL, NULL, OS-- list permissions for statements (S) and objects (O).

Viewing 6 posts - 1 through 5 (of 5 total)

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