These are chat archives for rails-sqlserver/activerecord-sqlserver-adapter

27th
Nov 2017
John Koisch
@jkoisch
Nov 27 2017 05:39
as near as I can tell via roles and permissions, the dbUser has access to the Information_SCHEMA. Is there some way to check?
Ken Collins
@metaskills
Nov 27 2017 12:24
Not that I know of off hand. But I can say that is your issue.
Dig a bit.
John Koisch
@jkoisch
Nov 27 2017 19:23

digging a lot ... through SMMS, this query returns 44 table names that are present in the dbo schema

SELECT t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE s.name = N'dbo';

this is true regardless of what database I hit (prod 1, prod2, dev 1, etc)

when I run that from rails c, i get

irb(main):006:0> results = Location.connection.exec_query("SELECT t.name FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] WHERE s.name = N'dbo'")
SQL (25.5ms) SELECT t.name FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] WHERE s.name = N'dbo'
=> #<ActiveRecord::Result:0x007fe390734800 @columns=["name"], @rows=[["spt_fallback_db"], ["spt_fallback_dev"], ["spt_fallback_usg"], ["spt_monitor"]], @hash_rows=nil, @column_types={}>

when pointing to the prod database where the query is failing. When I run it on the prod database where the Location query works, it sees all 44 tables as expected.

So, ultimately, i agree ... it is a perms issue.