how can i check whether role is working or not

Question

how can i check whether role is working or not

masterpiecebeta2

Right there with Babe

Points: 720
More actions
March 5, 2010 at 12:45 am

#386442

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?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

masterpiecebeta2 Right there with Babe Points: 720 More actions · Answer 1

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?

🙁

LutzM SSC Guru Points: 107049 More actions · Answer 2

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.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

masterpiecebeta2 Right there with Babe Points: 720 More actions · Answer 3

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

Lowell SSC Guru Points: 323482 More actions · Answer 4

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

masterpiecebeta2 Right there with Babe Points: 720 More actions · Answer 5

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');