Retrieving SQL Server 2005 Database Info Using SMO: Database Info, Table Info - How do we retrieve security info about all databases from a SQL Server instance using SMO?
(Page 4 of 5 )
Previous sections discussed dealing with basic and advanced information about databases. Now, in this section, I shall further extend what we are doing with some “security” information related to every database.
Before proceeding further, we again need to define a new structure to hold the database information. Let us look into that.
Public Function getStructDatabaseSecurity() As DataTable
Dim dt As New DataTable
With dt.Columns
.Add (New DataColumn ("Name"))
.Add (New DataColumn ("Schemas"))
.Add (New DataColumn ("Users"))
.Add (New DataColumn ("Roles"))
.Add (New DataColumn ("ApplicationRoles"))
.Add (New DataColumn ("Certificates"))
End With
Return dt
End Function
This is very similar to the one I defined in the first section. Now, let us proceed with the code to retrieve the information.
Imports Microsoft.SqlServer.Management.Smo
Public Class Form5
Private Sub Button1_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles Button1.Click
Dim svr As Server = New Server(".\sql2k5")
Dim db As Database
Dim dtReport As DataTable = util.getStructDatabaseSecurity
For Each db In svr.Databases
Dim drReport As DataRow = dtReport.NewRow
drReport ("Name") = db.Name
drReport ("Schemas") = db.Schemas.Count
drReport ("Users") = db.Users.Count
drReport ("Roles") = db.Roles.Count
drReport ("ApplicationRoles") = db.ApplicationRoles.Count
drReport ("Certificates") = db.Certificates.Count
dtReport.Rows.Add (drReport)
Next
Me.DataGridView1.DataSource = dtReport
End Sub
End Class
Next: How do we retrieve table information from a SQL Server database using SMO? >>
More MS SQL Server Articles
More By Jagadish Chaterjee
/ 12 
