How to tell *who* is using RMS in your environment quickly.

  • Article

I've had several customers ask me for a quick way to get a list of all of the users that are using RMS. The admin UI, will tell you how many people are, but not 'who' is using it.

Here is a vbscript I wrote that should tell you (assuming you have access to the RMS SQL server, locally, or remotely)

Due to my severe laziness the error trapping isn't that great...but I really just needed a quick sample to use as a model, and usually let my customers do the heavy error trapping in their own apps. This way I can truly say "Use at your own risk"...which I am also saying to you. ;)

Enjoy!

-Jason

'+++++++++++++++++++++++++++++++++++++++++++++++++++

' rmsusers.vbs - A simple script to match up the SDDL sids in the RMS

' dbase to user accounts

'+++++++++++++++++++++++++++++++++++++++++++++++++++

Option Explicit

Dim strComputer, strRoot, strDbase, strSQL, strConn
Dim objRoot
Dim conn,ldpconn
Dim rs,rsUser
Dim objArgs

Set objArgs = Wscript.Arguments

If objArgs.Count = 0 Then
ShowUsage
Wscript.Quit
End if

If lcase(objArgs(0)) = "internal" Then

strComputer = "np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query"

Else

strComputer = objArgs(0)

End If

Set objRoot = GetObject("LDAP://RootDSE")
strRoot = objRoot.Get("defaultnamingcontext")
Set objRoot = Nothing

Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.RecordSet")

With rs
.CursorLocation=3
.CursorType=0
End With

With conn
strDbase = "master"
GetConn()
.open strConn
strSQL = "Select Name From SysDatabases Where" & _
" Name like 'DRMS_Config%'"
rs.Open strSQL,conn
End With

If rs.RecordCount > 0 Then
strDbase = rs.Fields("name").Value
Wscript.Echo "++++Found Database:" & strDbase & ".++++" & vbcrlf & vbcrlf
rs.close
conn.close
strSQL = "Select s_Sid from UD_WindowsAuthIdentities"
GetConn()
conn.open strConn
rs.open strSQL,conn
If rs.RecordCount > 0 Then
Wscript.Echo "There were " & rs.RecordCount & " users found."
Do until rs.EOF
ResolveName(rs.Fields("s_Sid").Value)
rs.MoveNext
Loop
Else
Wscript.Echo "No users found in UD_WindowsAuthIdentities Table"
Cleanup
Wscript.Quit
End If
Else
Wcript.Echo "Could not find RMS Configuration Dbase"
CleanUp
Wscript.Quit
End if

'**********************************************************

'GetConn - Probably not needed. I thought I'd be making more connections

'**********************************************************

Sub GetConn()
strConn = "Provider=SQLOLEDB;Data Source=" & strComputer & ";" & _
"Trusted_Connection=Yes;Initial Catalog=" & strDbase
End Sub

'**********************************************************

'Resolve Name - Will spit out the user names based on SDDL sid

'**********************************************************

Sub ResolveName (strSID)
Set ldpconn = CreateObject("ADODB.Connection")
With ldpconn
.Provider = "ADSDSOObject"
.Open "ADSProvider"
strSQL = "<GC://" & strRoot & ">;(&(objectclass=user)(objectSID=" & strSID &"));name,mail;subtree"
Set rsUser = .Execute(strSQL)
If rsUser.RecordCount > 0 Then
Wscript.Echo "**-" & rsUser.Fields("name").Value & _
"-" & rsUser.Fields("mail").Value
Else
Wscript.Echo "Unresolved: " & strSID
End If
rsUser.Close
ldpconn.Close
Set rsuser = nothing
Set ldpconn = nothing
End with
End Sub

'*************************************

'CleanUp - If we bail - try to clean up

'*************************************

Sub CleanUp
rs.close
set rs = Nothing
conn.close
set conn=nothing
End Sub

'*************************************

'ShowUsage - How to use the script

'*************************************

Sub ShowUsage
Wscript.Echo "Usage:>cscript rmsusers.vbs rms_sql_server"
Wscript.Echo "Usage:>cscript rmsuser.vbs rms_sql_server\instance"

Wscript.Echo "Usage:>cscript rmsuser.vbs internal"
End Sub