I am trying to do a simple select: SELECT COUNT(*) FROM Contracts.
The login I am using has every right imaginable (db owner,admin, etc.). Plus on the table in question, it has Select, insert, delete, update and dri (whatever that is).
Whenever I try to run the query, I get "Select permission denied on object contracts" Error number -2147217911
Here is the calling routine:
Expand|Select|Wrap|Line Numbers
- s_SQL ="SELECT (COUNT(*) FROM Contracts"
- Set adoConnection = New ADODB.Connection
- adoConnection.CommandTimeout = 30
- adoConnection.Open gs_ConnectionString
- Set rs = New ADODB.Recordset
- Set rs = adoConnection.Execute(s_SQL)
- Set rs = New ADODB.Recordset
- rs.Open s_SQL, adoConnection, adOpenStatic, adLockReadOnly
Public Const gs_ConnectionString = "Provider=sqloledb;Data Source=[ip address],1433;" & _
"Network Library=DBMSSOCN;Initial Catalog=MyDatabase;" & _
"User ID=MyLogin;Password=MyPassword;"
Since the login has more than enough rights to do a select on the table, I suspect that this one of those errors that is really about something else.
Any ideas that I can try?