Revoke,Deny,Grant not working

Question

Post reply

Revoke,Deny,Grant not working

masterpiecebeta2

Right there with Babe

Points: 720
More actions
March 4, 2010 at 7:46 pm

#386441

create database asd
use asd
go
create login xyz with password='hihi'
create user masterpiece for login xyz
create schema schema1
alter user masterpiece with default_schema=schema1
create table schema1.paramore1(a int,b int)
insert into schema1.paramore1 values(1,2)
deny insert on schema ::schema1 to masterpiece
select *from schema1.paramore1
go
even i denied the insert permission from masterpiece still i can insert values why????????

Viewing 8 posts - 1 through 7 (of 7 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Which user were you logged in with when creating the table?

Which user were you logged in with when running the insert statement?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Did you run the deny prior to or after running the insert?

The order of your statements shows insert and then deny.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

hey pal the user is= masterpiece

i started new project with user masterpiece2 still got the problem

this is the sequenece that i used in management studio express sql 2005

create database asa

use asa

go

create login pqr with password='hihi'

create user masterpiece2 for login pqr

create schema schema3

alter user masterpiece2 with default_schema=schema3

create table schema3.paramore1(a int,b int)

select *from schema3.paramore1

deny insert on schema ::schema3 to masterpiece2

grant select on schema :: schema3 to masterpiece2

revoke select to masterpiece2

insert into schema3.paramore1 values(1,2)

select *from schema3.paramore1

go

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

masterpiecebeta2 (3/4/2010)
hey pal the user is= masterpiece

That doesn't answer my question though. You created that user for testing. Did you login with that user prior to creating that table?

Were you logged in with that use when trying to perform the insert?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

When I use your last set of code, I get successful (expected/desired) test results.

Msg 229, Level 14, State 5, Line 1

The INSERT permission was denied on the object 'paramore1', database 'asa', schema 'schema3'.

Msg 229, Level 14, State 5, Line 2

The SELECT permission was denied on the object 'paramore1', database 'asa', schema 'schema3'.

My steps are embedded with the code below

--Log in to server with admin account

create login pqr with password='BigL0ngp@ssword'

create user masterpiece2 for login pqr

go

create schema schema3

go

alter user masterpiece2 with default_schema=schema3

go

create table schema3.paramore1(a int,b int)

go

select *from schema3.paramore1

--create new connection for user 'pqr'

deny insert on schema ::schema3 to masterpiece2

--eliminate following two steps

--grant select on schema :: schema3 to masterpiece2

--revoke select to masterpiece2

--Log into server using 'pqr'

insert into schema3.paramore1 values(1,2)

select *from schema3.paramore1

--receive error messages as expected

go

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

i just try wat u send to me but unfortunately still it's inserting the values

its really infuriating

anyway thanks for your vital contribution

its Microsoft SqlServer Management Studio 2005

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Please attach a screenshot of the connection properties for the connection you are using to perform the insert statement.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events