Detect Users in SQL

reyboy · July 15th, 2003, 09:04 PM

Can anyone help me if how can i detect users currently accessing the SQL Server Database using any stored procedure or query(NT & SQL login)?

also

What Stored procedure or query should i use to know all the roles and grants to a user(NT & SQL login)?

Tnx!!!

David Cameron · July 15th, 2003, 09:13 PM

Look at sp_who and sp_who2.

For information on roles try sp_helprole.

regards
David Cameron

reyboy · July 15th, 2003, 11:05 PM

tnx!
I work on VB, how com this doesnt work? (ADO 2.6, VB 6.0 Ent)
-----------------------------------
cmd.ActiveConnection = SPConn
cmd.CommandText = "sp_helprolemember"
cmd.CommandType = adCmdStoredProc

cmd.Parameters.Refresh

'Set the param value.
cmd(1) = "db_owner"

Set rs = cmd.Execute
-----------------------------------
i get this error

"Item cannot be found in the collection corresponding to the requested name or ordinal."

but when i do it in query analyser it works just fine

David Cameron · July 15th, 2003, 11:14 PM

If you are going down that path you may find it easier to query the system tables directly. Try looking at sysusers.

Anway, your problem is that you haven't added the parameter to the command object, so you can't reference it. Try chaning your code to the following:

Code:

cmd.ActiveConnection = SPConn
cmd.CommandText = "sp_helprolemember"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("rolename", adVarChar, adParamInput, 20, "db_owner")

Set rs = cmd.Execute

regards
David Cameron

reyboy · July 16th, 2003, 09:15 PM

tnx!
i tried that one and got this err msg

Procedure or function sp_helprolemember has too many arguments specified.

David Cameron · July 16th, 2003, 09:19 PM

Post all your code.

regards
David Cameron

reyboy · July 17th, 2003, 09:14 PM

private sub getmerolemembers()

SPConn = "Provider=SQLOLEDB.1;Password='" & Password & "';User ID='" & UserName & "';Initial Catalog=databasename;Data Source='" & ServerName & "';Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=lordaeron;Use Encryption for Data=False;Tag with column collation when possible=False"

SPConn.Open

cmd.ActiveConnection = SPConn
cmd.CommandText = "sp_helprolemember"
cmd.CommandType = adCmdStoredProc

On Error GoTo errHandler
cmd.Parameters.Append cmd.CreateParameter("rolename", adVarChar, adParamInput, 20, Trim(List1))

'---
'error occurs here
Set rs = cmd.Execute
'---

rs.MoveFirst

Do While Not rs.EOF
    List2.AddItem rs(3)
    rs.MoveNext
Loop
Exit Sub

errHandler:
    msgbox err.description
End Sub
--------------------------------------
Note: The User Id default database is master and a db_accessadmin in the initial catalog used.
    "List1" is a listbox with database roles enumerated in it
--------------------------------------

David Cameron · July 17th, 2003, 09:19 PM

Where are your variables declared?
Where did you set the command, recordset and connection objects to their respective objects?

I'm guessing that this is done outside sub (which is a very bad practice). If so you have probably included parameters in the command object already, hence the error.

regards
David Cameron

reyboy · July 17th, 2003, 09:52 PM

Option Explicit
Dim cn1 As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
---------------------------
my form calls 2 Stored Procedure so i decided to call my connection in the form load and close it in the unload event.

If User Id default database is master and a db_accessadmin in the initial catalog used, will this effect in calling stored procedures?

Great help, tnx.

David Cameron · July 17th, 2003, 10:05 PM

I'll rewrite your sub in a way that make more sense to me. Bear in mind that I don't write much VB.

Code:

Private Function getmerolemembers(ByRef Connection As ADODB.Connection, ByVal RoleName as String) as Collection

    Dim cmdUser as new ADODB.Command
    Dim rsUser as new ADODB.Recordset
    Dim List as new Collection

    With cmdUser
        .ActiveConnection = Connection
        .CommandText = "sp_helprolemember"
        .CommandType = adCmdStoredProc
        .Parameters.Append cmd.CreateParameter("rolename", adVarChar, adParamInput, 20, RoleName)    
    End With    

    rsUser.CursorType = adOpenForwardOnly
    rsUser.LockType = adLockReadOnly
    Set rsUser = cmd.Execute

    Do While Not rsUser.EOF
        List.Add rsUser("MemberSID")
        rs.MoveNext
    Loop

    rsUser.Close

    getmerolemembers = List

End Function

regards
David Cameron