User per SQL Server level and instance.

  • Hi,

    I need to find out or collect information. Is there any setup and configuration check list for SQL RC ?

    1) How many user per SQL Server 2008 level and there password ?

    2) How many user per SQL Instance 2008 and there password ?

    3) SQL 2008 Database size, who is owner, data backup time and location, Size of disk ?

    After i collect those information i want to create a report

    4) How can i create a report of SQL Server 2008 setup and configuration ?

    Thnx in adv.

    Kahn

    New DB

  • Do you want to know the Maximum Capacity Specifications for SQL Server or do you want to check it on your server ? If its Maximum Capacity Specifications for SQL Server you can find it here http://msdn.microsoft.com/en-us/library/ms143432.aspx

    If you want to collect information for your server, you will have to query the system tables.

    Users select name from sys.syslogins

    Password -- It is not possible to retrieve the passwords.

    Dbsize sp_helpdb

    Diskspace exec master.dbo.xp_fixeddrives

    Last Backup Date of Databases

    SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),

    MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,

    ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as LastBackupDate

    FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A

    ON A.database_name = B.name AND A.type = 'D' GROUP BY B.Name ORDER BY B.name

    Sanz
  • Hello,

    thnx for your repaly, which is pretty usefull.

    Can i retriev password also ? for all user in database level and server ?

    thnx

  • Passwords are encrypted in SQL Server.

    Sanz
  • But you will be still able to transfer passwords along with logins to different server though.

  • google "sp_help_revlogin" - the 2005 version is compatible with 2008.

    It will script out the logins and passwords (hashed) where they can be run on another server.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

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

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