May 27, 2009 at 12:05 am
We have a Sql Server 2000 database (db1) that was migrated from SQL server 7.0 few years ago. Mixed mode of authentication is being used. I created a SQL Server login account say 'zzz' and associate it with the user 'zzz' in db1. The user zzz is by default member of public role and no other server role or security role (application role) is granted to the user. The public role hasn't got the permission on table say "t1 (owned by dbo)" and the user 'zzz' also doesn't have permission on this table as shown by the enterprise manager. However, the user can query the table 't1' but it should not. I tried to investigate and explicitly granted select permission to the user zzz on table t1. But when I again goto the enterprise manager to see the user zzz has the permission on table t1 it shows nothing. My question, how can the user zzz select rows from the table t1 if doesn't have any permission granted to it as shown by the enterprise manager.
May 27, 2009 at 3:07 am
Give the user db_datareader database role......
Tanx 😀
May 27, 2009 at 8:37 am
Does the user query the table directly via Query Analyzer or does it use a stored procedure? Try running this to see what permissions are granted for the table:
sp_helprotect @name = 'dbo.t1'
Greg
May 27, 2009 at 8:44 am
Maybe there is a permission granted to Public to select from that table?
-Roy
May 27, 2009 at 4:23 pm
Thanks Greg for your reply, but the sp_helprotect @name='dbo.t1' returns the following message:
"Server: Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 346
There are no matching rows on which to report"
I am using query analyser to query the table t1.
May 27, 2009 at 4:30 pm
the situation you describe does not ring true. Are you sure that
a) the user is not a member of some other user defined role that has select permissions on the table
b) the user does not have db_datareader role or any other fixed database role (not server role)
when you rechecked table permissions did you use the refresh button?.
---------------------------------------------------------------------
May 27, 2009 at 5:38 pm
Hi George, the situation I m describing is real not fake. We have two database servers with several databases on them. The database in question is running on server 1. The problem is only with this database not the others. I also tried to restore the database from backup on the another server but the problem remains the same. Just as you are thinking, my thoughts were also the same how it is possible. But really its happening I called up the support person yesterday and he also couldn't resolve the issue after spending 3 hrs. The user is not the member of any role (except public which is default and public has no permission on the table)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply