SQL Server Performance Forum – Threads Archive

About DB user privilege

Table "USERDB" stored the information of application ‘s users ,it’s owner is dbo. It has 3 columns :USERNAME ,PASSWORD ,USER_MARK Â©s there are 2 rows in it: USERNAME PASSWORD USER_MARK ——————————— AA AA A BB BB B The db have two user: user_A,user_B. My problem is :I want to make The DB user user_A just can select the rows which user_mark is "A" and user_B just can select the rows which user_mark is "B". I try to create a view for user_A like this: create view user_A.USERDB as select * from dbo.USERDB where USER_MARK=’A' with check option then user_A just can select the rows which user_mark is "A" with the sentence: select * from USERDB. But it also select all the rows of dbo.USERDB<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />elect * from dbo.USERDB. If I revoked the privilege that to prevent user_A to access table dbo.USERDB,then the view of user_A.USERDB can’t work. Help me .Thanks! Jelly.

Did you check public privileges are off?
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

The problem is occuring because you are using DBO to perform your SELECT, and the DBO user has permisson on these views. Instead create the two views using dbo as the owner. The through SQL Server security, allow user a to only access this view and deny b, and vice versa. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

Hi Jelly If you revoke permissions to user from TABLE but give him permissions to VIEW, it’ll work. VIEW does not need user to have table permissiosn. HTH
Harsh

What is the version of SQL & SP used? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forums This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

The user will need permission to both the table and the view IF the owner of the view is not also the owner of the table. If they are the same, then only permissions on the view are required. This is called the ownership chain. Pre SQL2000 SP3 this ownership chain also worked across databases, so if dbo owned both objects then you could still grant access only to the view. SP3 and later made this a configuration option as it is a security hole) Cheers
Twan

hi how can i now the privilege for each user in the sql server for exampple the default database

Refer to the books online about SP_HELPROTECT for complete information on the privileges. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.

]]>