October 20, 2009 at 4:26 am
Dear all,
I am getting stuck with a situation that cannot find what I did wrongly. Basically I have a student database and I create a custom role for this database as
IF NOT EXISTS (SELECT NAME FROM sysusers WHERE issqlrole=1 AND NAME = N'Student_Admin')
EXEC sp_addrole Student_Admin
GO
Then I loop through all tables and run script to deny all access to public to make sure that it secures. Then I grant access to Student_Admin Role as
GRANT SELECT, UPDATE, INSERT ON ... TO Student_Admin
Then I create new user and add this user to Student_Admin role as
if exists (select * from sys.syslogins where Name = N'user1')
Exec sp_droplogin N'user1'
GO
CREATE LOGIN [user1] WITH PASSWORD= N'abcde',DEFAULT_DATABASE=Student, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
CREATE USER [user1] FOR LOGIN [user1]
GO
EXEC sp_addrolemember [Student_Admin], [user1]
GO
It runs fine, but the problem is that the user1 cannot make any update or insert or select data from any table that the role Student_Admin is granted to access. I check with sp_helprotect and it shows that all tables are granted with select, insert, update for grantee Student_Admin
Please help. What i am doing wrongly?
October 20, 2009 at 4:31 am
Is that user in the public role? If I remember correctly DENY is evaluated before GRANT. I would probably leave public alone.
CEWII
October 20, 2009 at 5:28 am
Elliot is correct. If you have a conflict and a user belongs to different groups that gives him deny and grant permissions on the same object, the deny permission will be the one that takes the effect. Since everyone is a part of public and you denied all operations from public role, no one can do any operations on the table. The only exception is anyone that belongs to system administrator server group, because there permissions never gets checked.
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/
October 20, 2009 at 7:44 am
Fantastic. Thank you very much. I get it working now. I use REVOKE instead and it works fine.
Thanks,
October 20, 2009 at 1:19 pm
Chuoi Tieu (10/20/2009)
Fantastic. Thank you very much. I get it working now. I use REVOKE instead and it works fine.Thanks,
There is absolutely no reason to do anything in Public. You don't have to deny or revoke permissions from public because by default public does not have any access.
Are you granting access to all user tables? If so, you might want to lookup GRANT and check out the following syntax:
GRANT SELECT ON SCHEMA::dbo TO role;
This will grant select access on all objects in the dbo schema.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 21, 2009 at 11:42 pm
Jeffrey Williams-493691 (10/20/2009)
Chuoi Tieu (10/20/2009)
Fantastic. Thank you very much. I get it working now. I use REVOKE instead and it works fine.Thanks,
There is absolutely no reason to do anything in Public. You don't have to deny or revoke permissions from public because by default public does not have any access.
Are you granting access to all user tables? If so, you might want to lookup GRANT and check out the following syntax:
GRANT SELECT ON SCHEMA::dbo TO role;
This will grant select access on all objects in the dbo schema.
Thank you. You are absolutely right. I got it now.
October 22, 2009 at 3:58 am
One important different between granting permission on all existing objects and granting permission on the schema level, is the behavior when a new table is created at the schema. If you define the select permissions on the schema level, then the user will automatically have select permissions on any new table that will be created at the schema. If on the other hand you grant the permission at object level (e.g. table or view) that you’ll have to explicitly grant select permissions to each new object.
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/
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply