none
How to make list of all db's and users/rights on a sql server.

    Pertanyaan

  • How to make list of all db's and users/rights from a sql server?

    Hi guys sorry im kinda sql n00b but i really need to make inventory of this because we have multiple instances/db's on 1 server..

    Regards,

    28 Februari 2012 14:24

Jawaban

  • Hi,

    There is an online session conducted by SQLServerPedia (http://sqlserverpedia.com/wiki/SQL_Server_Training)and Quest Software about all DBA stuff I hope if you can see it.

    Here are many sample queries:

    List of all logins:

    SELECT L.name, L.denylogin, L.isntname, l.isntgroup, l.isntuser
    FROM master.sys.syslogins L
    WHERE l.sysadmin = 1 OR L.securityadmin = 1
    ORDER BY l.isntgroup, l.isntname, l.isntuser

    Names of Databases and last time it backed up:

    SELECT d.name, MAX(b.backup_finish_date) AS Last_Backup_Finish_Date
    FROM master.sys.databases d
    LEFT OUTER JOIN msdb.dbo.backupset b
    ON d.name = b.database_name AND b.type = 'D'
    WHERE d.database_id NOT IN(2,3)
    GROUP BY d.name
    ORDER BY 2

    I hope this is helpful.

    Elmozamil Elamir

    MyBlog



    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid
    http://elmozamil.blogspot.com

    29 Februari 2012 12:41
  • Hi,

    You may check this article for database permissions.

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    29 Februari 2012 15:20

Semua Balasan

  • Hi,

    I will give you an idea.

    Use the registered server option and create a group on edition wise

    Use the sys.logins table and get all the info whatever you want.



    Muthukkumaran Kaliyamoorthy

    Helping SQL DBAs and Developers >>> SqlserverBlogForum

    28 Februari 2012 16:26
  • Hi,

    There is an online session conducted by SQLServerPedia (http://sqlserverpedia.com/wiki/SQL_Server_Training)and Quest Software about all DBA stuff I hope if you can see it.

    Here are many sample queries:

    List of all logins:

    SELECT L.name, L.denylogin, L.isntname, l.isntgroup, l.isntuser
    FROM master.sys.syslogins L
    WHERE l.sysadmin = 1 OR L.securityadmin = 1
    ORDER BY l.isntgroup, l.isntname, l.isntuser

    Names of Databases and last time it backed up:

    SELECT d.name, MAX(b.backup_finish_date) AS Last_Backup_Finish_Date
    FROM master.sys.databases d
    LEFT OUTER JOIN msdb.dbo.backupset b
    ON d.name = b.database_name AND b.type = 'D'
    WHERE d.database_id NOT IN(2,3)
    GROUP BY d.name
    ORDER BY 2

    I hope this is helpful.

    Elmozamil Elamir

    MyBlog



    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid
    http://elmozamil.blogspot.com

    29 Februari 2012 12:41
  • Hi,

    You may check this article for database permissions.

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    29 Februari 2012 15:20