SQL views not accessible but tables are...

[Timo]

For some reason, an NT domain user is unable to see views in Access ADP. But
she can see the tables no problem. Is there a known issue with views? She
does have select permissions on the tables that underlie the view. Thanks in
advance for information or suggestions.
Timo

We're using SQL Server 2000 and Windows Server 2003 with Active Directory.
The connection string is using Integrated Security::

"Provider=sqloledb;Data Source=server1;Initial Catalog=ABC;Integrated
Security=SSPI;OLE DB Services=-2;"

Here are the steps I've taken with user permissions:

exec sp_grantlogin [OURDOMAIN\user99]
exec sp_grantdbaccess [OURDOMAIN\user99], 'SARAH'

exec sp_addrole 'TheRole'
exec sp_addrolemember 'TheRole', 'SARAH'


create view TestView
as select * from table1
inner join table2
on t1.id = t2.anotherid


grant select on table1 to TheRole
grant select on table2 to TheRole
grant select on TestView to TheRole

Database user SARAH can see the tables but not the view.

 

[Timo]

I should add that user SARAH can see the view from within Query Analyzer,
which I've just installed on her PC. So it seems to be an Access 2000
(perhaps in tandem with ActiveDirectory) issue. I installed the same ADP on
the desktop of our domain admin, and he had no trouble accessing the view.
Timo

 

[aaron.kempf]

is this Access 2000?

Access 2000 and SQL 2000 don't work together without a patch and that
patch is no longer published so you're shit out of luck

 

[Philipp Stiefel]

For some reason, an NT domain user is unable to see views in Access ADP. But
she can see the tables no problem. Is there a known issue with views? She
does have select permissions on the tables that underlie the view. Thanks in
advance for information or suggestions.
Timo

We're using SQL Server 2000 and Windows Server 2003 with Active Directory.
The connection string is using Integrated Security::

"Provider=sqloledb;Data Source=server1;Initial Catalog=ABC;Integrated
Security=SSPI;OLE DB Services=-2;"

Here are the steps I've taken with user permissions:

exec sp_grantlogin [OURDOMAIN\user99]
exec sp_grantdbaccess [OURDOMAIN\user99], 'SARAH'

exec sp_addrole 'TheRole'
exec sp_addrolemember 'TheRole', 'SARAH'


create view TestView
as select * from table1
inner join table2
on t1.id = t2.anotherid


grant select on table1 to TheRole
grant select on table2 to TheRole
grant select on TestView to TheRole

Database user SARAH can see the tables but not the view.

Is the dbo the owner of "TestView"?

What happens if you create a new form in Access and write
"SELECT * FROM TestView" as Recourdsource?

Cheers
Phil

 

[Timo]

Thanks for the suggestion, Philipp. Now it works. Yes, dbo was the owner of
TestView. TestView was the data source for a report, and it wasn't working
for user SARAH but it was working for me and for a user who is a domain
admin. Per your suggestion, I changed the recordsource to "Select * from
TestView" and now the report is working for user SARAH too.
Timo

For some reason, an NT domain user is unable to see views in Access ADP. But
she can see the tables no problem. Is there a known issue with views? She
does have select permissions on the tables that underlie the view. Thanks in
advance for information or suggestions.
Timo

We're using SQL Server 2000 and Windows Server 2003 with Active Directory.
The connection string is using Integrated Security::

"Provider=sqloledb;Data Source=server1;Initial Catalog=ABC;Integrated
Security=SSPI;OLE DB Services=-2;"

Here are the steps I've taken with user permissions:

exec sp_grantlogin [OURDOMAIN\user99]
exec sp_grantdbaccess [OURDOMAIN\user99], 'SARAH'

exec sp_addrole 'TheRole'
exec sp_addrolemember 'TheRole', 'SARAH'


create view TestView
as select * from table1
inner join table2
on t1.id = t2.anotherid


grant select on table1 to TheRole
grant select on table2 to TheRole
grant select on TestView to TheRole

Database user SARAH can see the tables but not the view.

Is the dbo the owner of "TestView"?

What happens if you create a new form in Access and write
"SELECT * FROM TestView" as Recourdsource?

Cheers
Phil