March 16, 2012 at 12:06 pm
Hello All,
I have 4 different schema on one database. Each schema is owned by an individual user within our team. No user can access tables within another schema. I am the sysadmin and have all rights on the server.
Now, I am trying to grant select permission to one particular table within one schema to the user who owns another schema. I am executing below sql and it runs successfully. It also add the permission on this tables permisison list but the user can not access the table.
GRANT SELECT ON [domain\67890].[table1] to [domain\12345]
GRANT SELECT ON OBJECT::[domain\67890].[table1] TO [domain\12345]
I also tried going to table propertie --> permisisons -- > add users and grant select but it still gives the same error while select.
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'test', database 'TestDB', schema 'domain\12345'.
Any suggestions... Thanks......
March 16, 2012 at 12:14 pm
If the user has both deny and grant permissions, the deny wins:-). Check if your user also had deny permissions on the table or if he is a member of a role that has deny permissions.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 16, 2012 at 12:34 pm
If I go to the user (in this database's security) who is granting the permission to the other user. And go to the securables tab I see All the other 3 schemas and few more tables listed. When I click on this particular schema (who needs permission to access one table from other schema) that has all the permissions denied. But, if I change the Select permission to Grant then he sees all the tables for another schema. We want only one table permission and not all.
I hope I am not confusing you.
March 16, 2012 at 12:39 pm
actually nothing is selected in Database Role Membership for both these users.
March 16, 2012 at 1:17 pm
Is there a DENY at the database or schema level where the table is?
K. Brian Kelley
@kbriankelley
March 16, 2012 at 1:28 pm
Is there a DENY at the database or schema level where the table is?
Yes, if I go to schema properties within database then at the permissions tab I see all the other 3 users listed. And below them there are permissions for each of them. There I see two lines for each of the permission like
select domain\12345 Grant With Grant Deny
select dbo Grant With Grant Deny
And there is is selected on Deny for all the permissions for all the users. And this is because we did not want to give permission for any user to do anything with other schema at all. Now we need to give object level permission where needed. So, if I click on Grant at listed above then it gives access to all the tables and not just the one.
March 16, 2012 at 1:46 pm
apat (3/16/2012)
And there is is selected on Deny for all the permissions for all the users. And this is because we did not want to give permission for any user to do anything with other schema at all. Now we need to give object level permission where needed. So, if I click on Grant at listed above then it gives access to all the tables and not just the one.
SQL Server is an explicit permission system. If you don't give a database user permission to an object, either directly or through a database role, that user doesn't have it. Therefore, you need to undo the DENYs. You only use those in cases where they DO have permissions and you can't undo the security model to remove their permissions and you don't want said user to have that permission.
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply