March 5, 2010 at 12:45 am
create login qwe with password='BigL0ngp@ssword'
create user masterpiece for login qwe
go
create schema schema1
go
alter user masterpiece with default_schema=schema1
go
create table schema1.paramore1(a int,b int)
go
create role doctor1
sp_addrolemember doctor,masterpiece
revoke insert ,update on schema1.paramore1 to doctor
go
now how can i check whether role is working or not
i typed
insert into schema1.paramore1 values(1,5)
i found still values can be inserted
if this is wrong what is the correct insert statement?
March 5, 2010 at 3:51 am
masterpiecebeta2 (3/5/2010)
create login qwe with password='BigL0ngp@ssword'create user masterpiece for login qwe
go
create schema schema1
go
alter user masterpiece with default_schema=schema1
go
create table schema1.paramore1(a int,b int)
go
create role doctor1
sp_addrolemember doctor,masterpiece
revoke insert ,update on schema1.paramore1 to doctor
go
now how can i check whether role is working or not
i typed
insert into schema1.paramore1 values(1,5)
i found still values can be inserted
if this is wrong what is the correct insert statement?
🙁
March 5, 2010 at 4:27 am
What user did you use to try the insert?
Your revoke statement will remove any previously given permission:
Straight from BOL:
Removes a previously granted or denied permission.
I guess what you're looking for is to DENY insert and update.
Furthermore, you didn't deny any permission from the role doctor1.
Your revoke statement refers to the user 'doctor'.
The easiest way to check if the permissions will be used is to connect to the database as the user you want to test permissions for.
March 5, 2010 at 6:16 am
hi dude i'm a newby for roles,it's little bit hard to understand, would you have any suitable tutorial links for a newby
March 5, 2010 at 6:27 am
masterpeice the part you are missing is how to test (along witht he DENY statement already identified.
here's a working example: note the EXECUTE AS and then the REVERT when i'm all done; that's the secret to testing the role/user.
USE SandBox;
GO
create login qwe with password='BigL0ngp@ssword'
create user masterpiece for login qwe
go
create schema schema1
go
alter user masterpiece with default_schema=schema1
go
create table schema1.paramore1(a int,b int)
go
create role doctor1
EXEC sp_addrolemember doctor,masterpiece
deny insert ,update on schema1.paramore1 to doctor
go
--##########################
--Test the user in the role
--##########################
execute as user='masterpiece' --changing to this user
--check security context
print user_name()
--try to do something the role is not allowed to do
insert into schema1.paramore1(a ,b )
SELECT 1,1 UNION ALL SELECT 2,2 --failed no permission
--Msg 229, Level 14, State 5, Line 6
--The INSERT permission was denied on the object 'paramore1', database 'SandBox', schema 'schema1'.
revert; --change back myself
Lowell
March 5, 2010 at 7:18 am
only i'd missed the statement
execute as user='masterpiece't
our lecturer never said anything about such a kind of stuff:angry:
anyway
thx u all guys helped me(':-D');
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply