Results 1 to 5 of 5

Thread: Security

  1. #1
    Join Date
    May 2004
    Location
    Bordentown, NJ
    Posts
    27

    Unanswered: Security

    I need to extract security data from all my SQL servers. Does anyone have a script that will display security data? ie. login name, type, permissions.......

    Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What version of SQL Server?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    What are you planning on doing with the info? Do you want a report or do you need to transfer the login info to another db instace?

    If you need a report you can start with this and build out what you need:
    Code:
    --SQL Server 2000
    Select * FROM master..sysxlogins
    
    --SQL Server 2005
    SELECT * FROM sys.server_principals l 
    LEFT OUTER JOIN sys.sql_logins s 
    ON l.SID = s.SID
    If you need to transfer the login info look into using sp_help_revlogin

    http://support.microsoft.com/kb/246133
    Last edited by Reghardt; 06-27-08 at 13:23.

  4. #4
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    even I need some help on this. I have 2005 and need to know what database role membership (like db_owner, db_datareader, db_datawriter...etc) a user has on DB's

    can someone tell me the sys view to use

  5. #5
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    Code:
    --This will give you server roles
    EXEC master..sp_helpsrvrolemember
    
    --This will give you database roles
    EXEC [dbname]..sp_helprolemember

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •