February 13, 2010 at 9:02 am
Hi,
I create on login name is Test. and i give the permissions
1. DB_DataReade and
2. DB_DenyDataReader
Is it possible to give these 2 roles in one login.
After assigning what will happened ?
please explain ....
February 13, 2010 at 9:38 am
subbarayudu.net 37258
From Books On Line (BOL) the help file for SQL Server:
Granting and Denying Permissions to Users and Roles
Permissions within a database are always granted to database users, roles, and Windows users or groups, but never to SQL Server 2000 logons. The methods used to set the appropriate permissions for users or roles within a database are: granting permissions, denying permissions, and revoking permissions.
The DENY permission allows an administrator to deny an object or statement permission to a user or role. As with Windows permissions, DENY takes precedence over all other permissions.
February 13, 2010 at 9:48 am
This looks like a test question. Have you not done this and observed what would happen? If this is one of your servers, set up a test database, do this, and see what happens.
February 15, 2010 at 6:46 pm
Hi Steave,
Its not a test question....
Once i create the user and I assigned those 2 database level roles.......
now that user not access the database,becasue of db_denydatareader overrite the db_datareader permissions.
Now i give the another permission to the same user i.e. db_datawriter....once i assigned thi i ll try to open the database,but db is not opened through left pane of ssms---rightclick of that database... it gives the error
permission required.
through ssms i insert the records........but
here my doubt is db_denydatareader is overite the only db_datareader or all other .
Subbu
February 15, 2010 at 9:09 pm
I'm not sure what you mean there. You need to a little more clearly, and detailed, explain.
Write permissions do not mean anything with read permissions. DenyReader is a permission to prevent SELECT statements. Nothing to do with INSERT, UPDATE, or DELETE statements.
February 15, 2010 at 9:48 pm
subbarayudu.net 37258 (2/15/2010)
Hi Steave,Its not a test question....
Once i create the user and I assigned those 2 database level roles.......
now that user not access the database, becasue of db_denydatareader overwrite the db_datareader permissions.
Now i give the another permission to the same user i.e. db_datawriter....once i assigned thi i ll try to open the database,but db is not opened through left pane of ssms---rightclick of that database... it gives the error
permission required.
through ssms i insert the records........but
here my doubt is db_denydatareader is overite the only db_datareader or all other .
Subbu
If i understood your question correct, here is the explanation. Deny overwrites all other permissions, even though you mentioned deny only for datareader "db_denydatareader, db_datareader". db_denydatareader will also over write db_datawriter, as it is like going from basic level permission to higher level permission
From BOL
The sp_helprotect system stored procedure reports permissions on a database-level securable.
Caution:
A table-level DENY does not take precedence over a column-level GRANT. This inconsistency in the permissions hierarchy has been preserved for the sake of backward compatibility. It will be removed in a future release.
Caution:
Denying CONTROL permission on a database implicitly denies CONNECT permission on the database. A principal that is denied CONTROL permission on a database will not be able to connect to that database.
Caution:
Denying CONTROL SERVER permission implicitly denies CONNECT SQL permission on the server. A principal that is denied CONTROL SERVER permission on a server will not be able to connect to that server.
Hope this helps,
EnjoY!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply