We have a third party application and wish to create a report based upon a view.
The strange thing is logged in to the server as a SQL SYSAdmin account, we cannot view the data via the view. SQL Admin accounts are setup correctly and there is nothing different on this particular server. No errors are returned just a blank view with no records.
Could this be a permissions problem or orphaned schemas in that particular database I thought SYSAdmin could view and do just about anything and the people who use this particular database would not have the know how on denying permission to the SYSAdmin role.
Thanks
N
SYSAdmin users do not have permission to see records in a view?
Andreas Georgsson
Thank you, I can see the logic in that. Nice way of denying sysadmin read permissions and have never come across it but one I won't forget...
Thanks
Neil
Chopta
You have to check the view definition to see what filters are applied.
Thanks
Laurentiu
Dimitrovski
There are 2 tables in this view. One of which is a 'Secure Employee' table which has a filter applied (WHERE FieldName = 'SUSER_SNAME()' )
If I remove this table from the view and rerun it returns all data. When added again it does not. So the SUSER_SNAME() has something to do with this issue. I'm not 100% sure, why sysAdmins could not view this dataset.
Regards
Neil
WidgetWorking
s_rastogi
Yes, a sysadmin user.
The view may have a filter applied. How would I find this out
rhinoishere
As Laurentiu mentioned, it seems like you are filtering the results with the predicate.
The predicate WHERE FieldName = 'SUSER_SNAME()' should filter out anything that doesn’t macth the criteria, in this case anything where FieldName is not equal to the login name (suser_sname() returns the current context login name).
Sysadmin really has the privileges to read all the data, but by applying this predicate you effectively removed all the rows from the results.
-Raul Garcia
SDE/T
SQL Server Engine
Queeez
Hi,
As far as I can tell there is nothing unusual with this view. Admin users in the front end application can see the result set, however SQL Administrators can not. If I script the view there is no unusual SQL code or encryption.
Regards
Neil
Bruxir
double post
bbossi
Just to make sure - by SQL administrator, you mean a member of the sysadmin server role, right
Does the view involve any predicates that may filter the results based on the current user context
Thanks
Laurentiu
ash141vsp2003
How is the view set up Was anyone ever able to see any records through it Maybe the view definition is incorrect.
Thanks
Laurentiu