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

Question

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,

Answer 1

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

Answer 2

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

Answer 3

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