Post reply

Substitue for sys.syslogins in SQL Server 2005?

  • June 16, 2010 at 7:51 am

    #223905

    Hi all,

    Since 'sys.syslogins' is now used as a Backward Compatibilty view in SQL Server 2005, Is there a substitue for this as a FUTURE PROOFING.

    We know that sys.sql_logins catalog view provides only the SQL logins. But sys.syslogins provides both SQL logins and Windows logins in SQL Server.

    My question: Is there any Security Catalog Views in SQL Server 2005 that can replace 'sys.syslogins' .

    I would like to know if its possible through JOIN too.

    John

  • June 16, 2010 at 8:31 am

    #1181027

    Try sys.server_principals in combination with sys.sql_logins

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 16, 2010 at 8:47 am

    #1181053

    Yes, it fulfills almost.

    But my aim here is to get the 'hasaccess' field which is in sys.syslogins.

    I could not find this field by joining a SQL 2005 sec. views

    John

  • June 16, 2010 at 8:50 am

    #1181060

    Or the 'denylogin' field in sys.syslogins

    John

  • June 16, 2010 at 9:20 am

    #1181095

    SELECT p.name, per.permission_name, per.state_desc

    FROM sys.server_principals p

    INNER JOIN sys.server_permissions per ON p.principal_id = per.grantee_principal_id

    WHERE per.type = 'COSQ'

    HasAccess is equivalent to 'GRANT' or 'GRANT_WITH_GRANT' states. DenyLogin is equivalent to the 'DENY' state

    p.s. sys.syslogins, sys.server_principals, sys.server_permissions are all views and their definitions can be viewed just like any other view. Hence it's possible to see, based on the actual system tables, how things in the views are defined.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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