cancel
Showing results for 
Search instead for 
Did you mean: 

Access To Users Table In Rules DB? (8.11.1)

Solution Partner Valued Contributor Solution Partner Valued Contributor
Solution Partner Valued Contributor

I have an app where I'm replicating some of the functionality in User Manager, and am accessing the Users table in the Rules DB to create a User subpart collection. The code to load the users looks like this:

 

...
        Try
            'Dim strSQL As String = "SELECT [User_ID],[User_Name],[Admin],[Disabled],[License_Key],[Project_Prefix],[Last_Login],[User_Email],[Password] FROM Users"
            Dim strSQL As String = "SELECT * FROM Users"
            Dim dsUsers As DataSet = g_RulesData.ExecuteSQL(strSQL)
            If dsUsers.Tables.Count > 0 Then
                For Each row As DataRow In dsUsers.Tables(0).Rows
...

Running either strSQL throws as an "Invalid SQL statement" exception on the bold line above.

 

I have very similar code to load the Roles that works fine:

...
Try Dim strSQL As String = "SELECT * FROM Roles" Dim dsRoles As DataSet = g_RulesData.ExecuteSQL(strSQL) If dsRoles.Tables.Count > 0 Then For Each row As DataRow In dsRoles.Tables(0).Rows
...

So, what's the difference? Permissions?

 

I had a version of this code working on 8.10.0, before moving to 8.11.1, but I was accessing the table using more code before discovering the more direct access through g_RulesData.ExecuteSQL(strSQL). I can't be sure this worked in 8.10.0, and I uninstalled all old versions while getting the thin client running. 

4 REPLIES

Re: Access To Users Table In Rules DB? (8.11.1)

Siemens Experimenter Siemens Experimenter
Siemens Experimenter

Chris,

 

Since "Users" means something to SQL Server, try changing your query to use:

FROM [dbo].[Users]

so that SQL Server knows that you are referring to the table.

 

/gdm

Re: Access To Users Table In Rules DB? (8.11.1)

Experimenter
Experimenter

Hello,

 

Is the proposed solution working for you in 8.11.0 or higher? It is not working for me. Is perhaps the Users table locked (i.e. you don't have access rights from RSEngineer)?

 

Thanks,
Mark

Re: Access To Users Table In Rules DB? (8.11.1)

Solution Partner Valued Contributor Solution Partner Valued Contributor
Solution Partner Valued Contributor
Hi Mark,

I haven't been back to this, as I ended up going another route.

Re: Access To Users Table In Rules DB? (8.11.1)

Siemens Experimenter Siemens Experimenter
Siemens Experimenter

Mark & Chris,

There are a list of tables that Access is not permitted via ExecuteSQL():

syscolumns
syscomments
sysdepends
sysfilegroups
sysfiles
sysfiles1
sysforeignkeys
sysfulltextcatalogs
sysfulltextnotify
sysindexes
sysindexkeys
sysmembers
sysobjects
syspermissions
sysproperties
sysprotects sysreferences
systypes
sysusers
users
sitesettings

MarkZeinstra wrote:

Hello,

 

Is the proposed solution working for you in 8.11.0 or higher? It is not working for me. Is perhaps the Users table locked (i.e. you don't have access rights from RSEngineer)?

 

Thanks,
Mark