How to enumerate users in a Role SQL-DMO

Hi there,
Is there a way that I can use SQL-DMO to enumerate the users assigned to a particular role? If yes how can I do that? Books online talks about using EnumDatabaseRoleMember but does not elaborate.
Any help will be greatly appreciated.
Thanks!
hi Jim,
"Jim" <Jim@.discussions.microsoft.com> ha scritto nel messaggio
news:C0AAE3C8-1FCF-4133-B538-61EB2C5FAB79@.microsoft.com...
> Hi there,
> Is there a way that I can use SQL-DMO to enumerate the users
>assigned to a particular role? If yes how can I do that? Books
>online talks about using EnumDatabaseRoleMember but does not elaborate.
> Any help will be greatly appreciated.
> Thanks!
Dim i As Integer
Dim lErr As Long, sErr As String
Dim oRole As SQLDMO.DatabaseRole
Dim oQry As SQLDMO.QueryResults
Set oRole = objServer.Databases(sDBname).DatabaseRoles(sRoleNa me)
If Not oRole.AppRole Then
On Local Error Resume Next
Set oQry = oRole.EnumDatabaseRoleMember
lErr = Err.Number
sErr = Err.Description
On Local Error GoTo 0
If lErr = 0 And Not oQry Is Nothing Then
With oQry
If .ResultSets <> 0 Then
.CurrentResultSet = 1
For i = 1 To .Rows
Debug.Print .GetColumnString(i, 1)
Next
End If
End With
End If
Else
Debug.Print "Application Role"
End If
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||thanks so much . it worked
"Andrea Montanari" wrote:

> hi Jim,
> "Jim" <Jim@.discussions.microsoft.com> ha scritto nel messaggio
> news:C0AAE3C8-1FCF-4133-B538-61EB2C5FAB79@.microsoft.com...
>
> Dim i As Integer
> Dim lErr As Long, sErr As String
> Dim oRole As SQLDMO.DatabaseRole
> Dim oQry As SQLDMO.QueryResults
> Set oRole = objServer.Databases(sDBname).DatabaseRoles(sRoleNa me)
> If Not oRole.AppRole Then
> On Local Error Resume Next
> Set oQry = oRole.EnumDatabaseRoleMember
> lErr = Err.Number
> sErr = Err.Description
> On Local Error GoTo 0
> If lErr = 0 And Not oQry Is Nothing Then
> With oQry
> If .ResultSets <> 0 Then
> .CurrentResultSet = 1
> For i = 1 To .Rows
> Debug.Print .GetColumnString(i, 1)
> Next
> End If
> End With
> End If
> Else
> Debug.Print "Application Role"
> End If
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

How to enumerate users in a Role SQL-DMO

Hi there,
Is there a way that I can use SQL-DMO to enumerate the users assigned to a particular role? If yes how can I do that? Books online talks about using EnumDatabaseRoleMember but does not elaborate.
Any help will be greatly appreciated.
Thanks!hi Jim,
"Jim" <Jim@.discussions.microsoft.com> ha scritto nel messaggio
news:C0AAE3C8-1FCF-4133-B538-61EB2C5FAB79@.microsoft.com...
> Hi there,
> Is there a way that I can use SQL-DMO to enumerate the users
>assigned to a particular role? If yes how can I do that? Books
>online talks about using EnumDatabaseRoleMember but does not elaborate.
> Any help will be greatly appreciated.
> Thanks!
Dim i As Integer
Dim lErr As Long, sErr As String
Dim oRole As SQLDMO.DatabaseRole
Dim oQry As SQLDMO.QueryResults
Set oRole = objServer.Databases(sDBname).DatabaseRoles(sRoleName)
If Not oRole.AppRole Then
On Local Error Resume Next
Set oQry = oRole.EnumDatabaseRoleMember
lErr = Err.Number
sErr = Err.Description
On Local Error GoTo 0
If lErr = 0 And Not oQry Is Nothing Then
With oQry
If .ResultSets <> 0 Then
.CurrentResultSet = 1
For i = 1 To .Rows
Debug.Print .GetColumnString(i, 1)
Next
End If
End With
End If
Else
Debug.Print "Application Role"
End If
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||thanks so much . it worked
"Andrea Montanari" wrote:
> hi Jim,
> "Jim" <Jim@.discussions.microsoft.com> ha scritto nel messaggio
> news:C0AAE3C8-1FCF-4133-B538-61EB2C5FAB79@.microsoft.com...
> > Hi there,
> > Is there a way that I can use SQL-DMO to enumerate the users
> >assigned to a particular role? If yes how can I do that? Books
> >online talks about using EnumDatabaseRoleMember but does not elaborate.
> >
> > Any help will be greatly appreciated.
> >
> > Thanks!
>
> Dim i As Integer
> Dim lErr As Long, sErr As String
> Dim oRole As SQLDMO.DatabaseRole
> Dim oQry As SQLDMO.QueryResults
> Set oRole = objServer.Databases(sDBname).DatabaseRoles(sRoleName)
> If Not oRole.AppRole Then
> On Local Error Resume Next
> Set oQry = oRole.EnumDatabaseRoleMember
> lErr = Err.Number
> sErr = Err.Description
> On Local Error GoTo 0
> If lErr = 0 And Not oQry Is Nothing Then
> With oQry
> If .ResultSets <> 0 Then
> .CurrentResultSet = 1
> For i = 1 To .Rows
> Debug.Print .GetColumnString(i, 1)
> Next
> End If
> End With
> End If
> Else
> Debug.Print "Application Role"
> End If
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>sql

How to enumerate users in a Role SQL-DMO

Hi there,
Is there a way that I can use SQL-DMO to enumerate the users assigned to a p
articular role? If yes how can I do that? Books online talks about using E
numDatabaseRoleMember but does not elaborate.
Any help will be greatly appreciated.
Thanks!hi Jim,
"Jim" <Jim@.discussions.microsoft.com> ha scritto nel messaggio
news:C0AAE3C8-1FCF-4133-B538-61EB2C5FAB79@.microsoft.com...
> Hi there,
> Is there a way that I can use SQL-DMO to enumerate the users
>assigned to a particular role? If yes how can I do that? Books
>online talks about using EnumDatabaseRoleMember but does not elaborate.
> Any help will be greatly appreciated.
> Thanks!
Dim i As Integer
Dim lErr As Long, sErr As String
Dim oRole As SQLDMO.DatabaseRole
Dim oQry As SQLDMO.QueryResults
Set oRole = objServer.Databases(sDBname).DatabaseRoles(sRoleName)
If Not oRole.AppRole Then
On Local Error Resume Next
Set oQry = oRole.EnumDatabaseRoleMember
lErr = Err.Number
sErr = Err.Description
On Local Error GoTo 0
If lErr = 0 And Not oQry Is Nothing Then
With oQry
If .ResultSets <> 0 Then
.CurrentResultSet = 1
For i = 1 To .Rows
Debug.Print .GetColumnString(i, 1)
Next
End If
End With
End If
Else
Debug.Print "Application Role"
End If
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||thanks so much . it worked
"Andrea Montanari" wrote:

> hi Jim,
> "Jim" <Jim@.discussions.microsoft.com> ha scritto nel messaggio
> news:C0AAE3C8-1FCF-4133-B538-61EB2C5FAB79@.microsoft.com...
>
> Dim i As Integer
> Dim lErr As Long, sErr As String
> Dim oRole As SQLDMO.DatabaseRole
> Dim oQry As SQLDMO.QueryResults
> Set oRole = objServer.Databases(sDBname).DatabaseRoles(sRoleName)
> If Not oRole.AppRole Then
> On Local Error Resume Next
> Set oQry = oRole.EnumDatabaseRoleMember
> lErr = Err.Number
> sErr = Err.Description
> On Local Error GoTo 0
> If lErr = 0 And Not oQry Is Nothing Then
> With oQry
> If .ResultSets <> 0 Then
> .CurrentResultSet = 1
> For i = 1 To .Rows
> Debug.Print .GetColumnString(i, 1)
> Next
> End If
> End With
> End If
> Else
> Debug.Print "Application Role"
> End If
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

How to enable SMO and SQL-DMO

When I installed SqlServer 2005 SP1, a warning message stated that I need to
install Hotfix 2047 or higher. I searched MSDN website and found Cumulative
hotfix package (buld 2153) for SQL Server 2005.
If I understand correctly there are 6 different hotfixes that must be
applied in a particular sequence and I must enable something called Server
Management Object (SMO), and SQL Server Distributed Management Object
(SQL-DMO) before installing the hotfix package.
"You must enable the SQL Server Management Object (SMO) and SQL Server
Distributed Management Object (SQL-DMO) extended stored procedures before
you install the hotfix package. For more information about the SMO/DMO XPs
option, see SQL Server 2005 Books Online."
I have searched Books Online extensively and found nothing specific about
enablling SMO and SQL-DMO. Can someone please give me the short version of
how to do this?
Thanks,
KeithThey are both enabled by default. If you want to verify, use sp_configure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"keith" <kbrickey@.dslextreme.com> wrote in message news:OPxx0cUTHHA.480@.TK2MSFTNGP02.phx.gbl
..
> When I installed SqlServer 2005 SP1, a warning message stated that I need
to
> install Hotfix 2047 or higher. I searched MSDN website and found Cumulativ
e
> hotfix package (buld 2153) for SQL Server 2005.
> If I understand correctly there are 6 different hotfixes that must be
> applied in a particular sequence and I must enable something called Server
> Management Object (SMO), and SQL Server Distributed Management Object
> (SQL-DMO) before installing the hotfix package.
> "You must enable the SQL Server Management Object (SMO) and SQL Server
> Distributed Management Object (SQL-DMO) extended stored procedures before
> you install the hotfix package. For more information about the SMO/DMO XPs
> option, see SQL Server 2005 Books Online."
> I have searched Books Online extensively and found nothing specific about
> enablling SMO and SQL-DMO. Can someone please give me the short version of
> how to do this?
> Thanks,
> Keith
>sql

How to enable SMO and SQL-DMO

When I installed SqlServer 2005 SP1, a warning message stated that I need to
install Hotfix 2047 or higher. I searched MSDN website and found Cumulative
hotfix package (buld 2153) for SQL Server 2005.
If I understand correctly there are 6 different hotfixes that must be
applied in a particular sequence and I must enable something called Server
Management Object (SMO), and SQL Server Distributed Management Object
(SQL-DMO) before installing the hotfix package.
"You must enable the SQL Server Management Object (SMO) and SQL Server
Distributed Management Object (SQL-DMO) extended stored procedures before
you install the hotfix package. For more information about the SMO/DMO XPs
option, see SQL Server 2005 Books Online."
I have searched Books Online extensively and found nothing specific about
enablling SMO and SQL-DMO. Can someone please give me the short version of
how to do this?
Thanks,
KeithThey are both enabled by default. If you want to verify, use sp_configure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"keith" <kbrickey@.dslextreme.com> wrote in message news:OPxx0cUTHHA.480@.TK2MSFTNGP02.phx.gbl...
> When I installed SqlServer 2005 SP1, a warning message stated that I need to
> install Hotfix 2047 or higher. I searched MSDN website and found Cumulative
> hotfix package (buld 2153) for SQL Server 2005.
> If I understand correctly there are 6 different hotfixes that must be
> applied in a particular sequence and I must enable something called Server
> Management Object (SMO), and SQL Server Distributed Management Object
> (SQL-DMO) before installing the hotfix package.
> "You must enable the SQL Server Management Object (SMO) and SQL Server
> Distributed Management Object (SQL-DMO) extended stored procedures before
> you install the hotfix package. For more information about the SMO/DMO XPs
> option, see SQL Server 2005 Books Online."
> I have searched Books Online extensively and found nothing specific about
> enablling SMO and SQL-DMO. Can someone please give me the short version of
> how to do this?
> Thanks,
> Keith
>