DMO/SMO - List SQL Server user permissions
Hi All,
Is there anybody know how can I list SQL Server Database Users and their permissions (Owned Schemas, Role Members, DB and Table securables) with DMO or SMO?
I can List SQL Server Databases, Tables and Logins with these functions;
Code:
Public Function ListAllDB() As ArrayList
Dim DBList As New ArrayList()
Try
For Each db As SQLDMO.Database In Server.Databases
If Not DBList.Contains(db.Name) Then
ComboBox1.Items.Add(db.Name)
End If
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Return DBList
End Function
Code:
Public Function ListAllTables(ByVal db_name As String) As ArrayList
Dim TableList As New ArrayList()
Try
DBase = DirectCast(Server.Databases.Item(db_name, Nothing), SQLDMO.Database)
For Each tbl As Table In DBase.Tables
If Not (tbl.Name.Substring(0, 3) = "sys") Then
If Not TableList.Contains(tbl.Name) Then
ListBox1.Items.Add(tbl.Name)
End If
End If
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Return TableList
End Function
Code:
Public Function ListLogins() As ArrayList
Dim LoginList As New ArrayList()
Try
For Each Login As SQLDMO.Login In Server.Logins
If Not (Login.Name.Substring(0, 2) = "##") Then
If Not LoginList.Contains(Login.Name) Then
ListBox2.Items.Add(Login.Name)
End If
End If
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Return LoginList
End Function
Thank you.