October 3, 2012 at 8:13 am
A user with appropriate privileges is able to access view with some outputs while the users with system administrator privilages are not able to get any outputs from the view, while when querying the view the t-sql is executed successfully but with no outputs.
Any views on how to resolve the issue!
October 3, 2012 at 8:34 am
theres a view called as filtered product,
select * from dbo.filteredproduct
the result of this query gives around 25 outputs.
while the same query gives 0 output for system administrators.
Also the instance is not restored from another server.
October 3, 2012 at 8:36 am
Is the view filtered based on row level security, eg a User can only see Certain Products?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 3, 2012 at 8:39 am
No its not filtered, i also tried giving myself view level access on this particular view but that also didnt work out.
October 3, 2012 at 8:43 am
Are you able to post the view definition, please?
John
October 3, 2012 at 8:45 am
before that i want to ask one thing,
Another user of appropriate privilages is able to access data from that view while the system administrator is not able to!
This means the view is intact and correct right?
October 3, 2012 at 8:55 am
I can't say whether it's correct or not because I can't see your data. But since one user gets one result set and another user gets a different one, the only thing I can think of is that there is something user-sensitive in the view. That's why I'm interested in the definition.
John
October 3, 2012 at 8:56 am
Use this function to establish the permissions of both users.
SELECT * FROM fn_my_permissions(NULL, 'SERVER');
GO
SELECT * FROM fn_my_permissions('Sales.vIndividualCustomer', 'OBJECT')
ORDER BY subentity_name, permission_name ;
GO
http://msdn.microsoft.com/en-us/library/ms176097.aspx
It sounds to me that the "sysadmin" is not a a sysadmin. Possibly using to different sql servers or databases.
October 3, 2012 at 8:59 am
sql-noob (10/3/2012)
No its not filtered, i also tried giving myself view level access on this particular view but that also didnt work out.
The reasons for asking the question were
1) As John said, if different users get different results there is something specific
2) The name of the view dbo.filteredProduct suggests that there is some form of filtering going on.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 3, 2012 at 9:07 am
The user who is able to access the view belongs to a group which is added to the sql server from ADS(active directory services). Now the irony is other users belonging to the same group are not able to view the data from the view(although command executed successfully).
In addition the sa's are also not able to get the data from the view.
CONFESSION: I am very sorry, but i think i won't be able to post the view definition because i think its against the company's policy and i am a fresher here! Please understand my concerns.
October 3, 2012 at 9:14 am
OK, understood. Does the definition contain "user_name", "user_id", "sys.database_principals", "current_user" or anything else that suggests it's returning data according to the user running the query?
John
October 3, 2012 at 9:15 am
sql-noob (10/3/2012)
The user who is able to access the view belongs to a group which is added to the sql server from ADS(active directory services). Now the irony is other users belonging to the same group are not able to view the data from the view(although command executed successfully).In addition the sa's are also not able to get the data from the view.
CONFESSION: I am very sorry, but i think i won't be able to post the view definition because i think its against the company's policy and i am a fresher here! Please understand my concerns.
Do you work in a Financial institute (hedge fund, investment bank etc)?
As your description fits the type of thing that I've seen before at such institutions, such that only select people are able to access certain data, and even the SA account is forbidden from seeing that data as its commercially sensitive.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 3, 2012 at 9:21 am
I apologize. I misread the original post that suggested there was an issue with permissions.
Data is not restricted on a permission basis. Either you can read the data or you cannot and you get a select error (unless the application handles the error).
Either the queries being ran are different or they are looking at another database. I should look at running a trace against the database in question to confirm that
a) that the queries are being ran against the same database
n) that the queries are identical
John Mitchell-245523 (10/3/2012)
OK, understood. Does the definition contain "user_name", "user_id", "sys.database_principals", "current_user" or anything else that suggests it's returning data according to the user running the query?John
This is probably an outside chance but is the only way data "might" be filtered by user.
Are you using a bespoke application with custom security levels?
October 3, 2012 at 11:29 am
Yes it does contain the constraints which results in giving user specific results, my only concern is that it's applied only to the user who is logging through the application not for the users who login's directly on to the database.
I tried editing the view and querying the tables individually the view was using but that was giving different output than the user who is not sa but logs on to the database directly.
won't the sa has the ability to see everything!?
October 3, 2012 at 11:31 am
John Mitchell-245523 (10/3/2012)
OK, understood. Does the definition contain "user_name", "user_id", "sys.database_principals", "current_user" or anything else that suggests it's returning data according to the user running the query?John
Yes it does contain the constraints which results in giving user specific results, my only concern is that it's applied only to the user who is logging through the application not for the users who login's directly on to the database.
I tried editing the view and querying the tables individually the view was using but that was giving different output than the user who is not sa but logs on to the database directly.
won't the sa has the ability to see everything!?
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply