Hi,
I have a database with 500+ tables and 1000+ views and equal no of
stored procedures. I have a sql server login id which I have included
in the following roles:
db_datareader
db_datawriter
now the issues is.. i want to revoke delete permission from certain
tables. Any revoke or deny do not affect the behaviour i.e. even after
successfully executing revoke/deny statements on these tables for
DELETE statement.. user is able to DELETE.
pl advise the best way to revoke delete permission from a few (half
dozen) tables out of the total.
regards,
D2
D2,
Well, I think that REVOKE will not help you much, because still the login is
part of the db_datawriter role, but DENY should.
How is that login deleting the rows, using a stored procedure created by a
user with greater rights?
AMB
"D2" wrote:
> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2
>
|||> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
As Alejandro mentioned, DENY takes precedence over granted permissions so if
a DENY DELETE should override the DELETE granted by db_datawriter
membership. Note that a REVOKE will remove a previous GRANT or DENY so if
you first execute a DENY and then a REVOKE, the DENY is lost.
Also, keep in mind that ownership chaining overrides permissions when
objects are accessed indirectly, If the user has execute permissions on a
stored procedure, permissions on indirectly used objects are not needed as
long as the objects involved have the same owner.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"D2" <dhapola@.yahoo.com> wrote in message
news:9f959c94-c6d0-4f9e-8098-0b51ce7de26a@.e6g2000prf.googlegroups.com...
> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2
Showing posts with label delete. Show all posts
Showing posts with label delete. Show all posts
Tuesday, March 20, 2012
revoke delete permission from large no of tables
Hi,
I have a database with 500+ tables and 1000+ views and equal no of
stored procedures. I have a sql server login id which I have included
in the following roles:
db_datareader
db_datawriter
now the issues is.. i want to revoke delete permission from certain
tables. Any revoke or deny do not affect the behaviour i.e. even after
successfully executing revoke/deny statements on these tables for
DELETE statement.. user is able to DELETE.
pl advise the best way to revoke delete permission from a few (half
dozen) tables out of the total.
regards,
D2D2,
Well, I think that REVOKE will not help you much, because still the login is
part of the db_datawriter role, but DENY should.
How is that login deleting the rows, using a stored procedure created by a
user with greater rights?
AMB
"D2" wrote:
> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2
>|||> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
As Alejandro mentioned, DENY takes precedence over granted permissions so if
a DENY DELETE should override the DELETE granted by db_datawriter
membership. Note that a REVOKE will remove a previous GRANT or DENY so if
you first execute a DENY and then a REVOKE, the DENY is lost.
Also, keep in mind that ownership chaining overrides permissions when
objects are accessed indirectly, If the user has execute permissions on a
stored procedure, permissions on indirectly used objects are not needed as
long as the objects involved have the same owner.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"D2" <dhapola@.yahoo.com> wrote in message
news:9f959c94-c6d0-4f9e-8098-0b51ce7de26a@.e6g2000prf.googlegroups.com...
> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2
I have a database with 500+ tables and 1000+ views and equal no of
stored procedures. I have a sql server login id which I have included
in the following roles:
db_datareader
db_datawriter
now the issues is.. i want to revoke delete permission from certain
tables. Any revoke or deny do not affect the behaviour i.e. even after
successfully executing revoke/deny statements on these tables for
DELETE statement.. user is able to DELETE.
pl advise the best way to revoke delete permission from a few (half
dozen) tables out of the total.
regards,
D2D2,
Well, I think that REVOKE will not help you much, because still the login is
part of the db_datawriter role, but DENY should.
How is that login deleting the rows, using a stored procedure created by a
user with greater rights?
AMB
"D2" wrote:
> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2
>|||> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
As Alejandro mentioned, DENY takes precedence over granted permissions so if
a DENY DELETE should override the DELETE granted by db_datawriter
membership. Note that a REVOKE will remove a previous GRANT or DENY so if
you first execute a DENY and then a REVOKE, the DENY is lost.
Also, keep in mind that ownership chaining overrides permissions when
objects are accessed indirectly, If the user has execute permissions on a
stored procedure, permissions on indirectly used objects are not needed as
long as the objects involved have the same owner.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"D2" <dhapola@.yahoo.com> wrote in message
news:9f959c94-c6d0-4f9e-8098-0b51ce7de26a@.e6g2000prf.googlegroups.com...
> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2
Wednesday, March 7, 2012
returning the table that caused a contraint error
HI all,
I have, as per normal, relations setup with delete contraints on the parent
child relation.
Now I have a table that has many such child relations and constraint. As a
result when a user tries to delete the parent, a correct message is
triggered.
My problem is, trying to determine which table is the cause of this
contraint error. I can see the table mentioned in the 547 error that SQL
produces. I was wondering if there is a SP or something that could return
just the table name, that I could use, instead of wading through the error
message.
Thanks
Robert
hi Robert,
Robert Bravery wrote:
> HI all,
> I have, as per normal, relations setup with delete contraints on the
> parent child relation.
> Now I have a table that has many such child relations and constraint.
> As a result when a user tries to delete the parent, a correct message
> is triggered.
> My problem is, trying to determine which table is the cause of this
> contraint error. I can see the table mentioned in the 547 error that
> SQL produces. I was wondering if there is a SP or something that
> could return just the table name, that I could use, instead of wading
> through the error message.
this should be the scenario you mean...
SET NOCOUNT ON
USE tempdb ;
GO
CREATE TABLE dbo.MasterT (
ID int NOT NULL PRIMARY KEY ,
Data varchar(10) NOT NULL
) ;
CREATE TABLE dbo.DetailT (
ID int NOT NULL PRIMARY KEY ,
IdMasterT int NOT NULL
CONSTRAINT FK_MasterT_DetailT
FOREIGN KEY REFERENCES dbo.MasterT (ID),
Data varchar(10) NOT NULL
) ;
GO
INSERT INTO dbo.MasterT VALUES ( 1 , 'value' ) ;
INSERT INTO dbo.MasterT VALUES ( 2 , 'value' ) ;
INSERT INTO dbo.DetailT VALUES ( 1 , 1, 'value' ) ;
INSERT INTO dbo.DetailT VALUES ( 2 , 1, 'value' ) ;
INSERT INTO dbo.DetailT VALUES ( 3 , 2, 'value' ) ;
GO
DELETE dbo.MasterT
WHERE ID = 1 ;
GO
DROP TABLE dbo.DetailT , dbo.MasterT ;
all you can get is the error you already noted, 547, which reports the
actual problem indicating the constraint name, the involved table and colum,
Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK_MasterT_DetailT'. The conflict occurred in database 'tempdb', table
'DetailT', column 'IdMasterT'.
The statement has been terminated.
no other way is allowed/possible...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
I have, as per normal, relations setup with delete contraints on the parent
child relation.
Now I have a table that has many such child relations and constraint. As a
result when a user tries to delete the parent, a correct message is
triggered.
My problem is, trying to determine which table is the cause of this
contraint error. I can see the table mentioned in the 547 error that SQL
produces. I was wondering if there is a SP or something that could return
just the table name, that I could use, instead of wading through the error
message.
Thanks
Robert
hi Robert,
Robert Bravery wrote:
> HI all,
> I have, as per normal, relations setup with delete contraints on the
> parent child relation.
> Now I have a table that has many such child relations and constraint.
> As a result when a user tries to delete the parent, a correct message
> is triggered.
> My problem is, trying to determine which table is the cause of this
> contraint error. I can see the table mentioned in the 547 error that
> SQL produces. I was wondering if there is a SP or something that
> could return just the table name, that I could use, instead of wading
> through the error message.
this should be the scenario you mean...
SET NOCOUNT ON
USE tempdb ;
GO
CREATE TABLE dbo.MasterT (
ID int NOT NULL PRIMARY KEY ,
Data varchar(10) NOT NULL
) ;
CREATE TABLE dbo.DetailT (
ID int NOT NULL PRIMARY KEY ,
IdMasterT int NOT NULL
CONSTRAINT FK_MasterT_DetailT
FOREIGN KEY REFERENCES dbo.MasterT (ID),
Data varchar(10) NOT NULL
) ;
GO
INSERT INTO dbo.MasterT VALUES ( 1 , 'value' ) ;
INSERT INTO dbo.MasterT VALUES ( 2 , 'value' ) ;
INSERT INTO dbo.DetailT VALUES ( 1 , 1, 'value' ) ;
INSERT INTO dbo.DetailT VALUES ( 2 , 1, 'value' ) ;
INSERT INTO dbo.DetailT VALUES ( 3 , 2, 'value' ) ;
GO
DELETE dbo.MasterT
WHERE ID = 1 ;
GO
DROP TABLE dbo.DetailT , dbo.MasterT ;
all you can get is the error you already noted, 547, which reports the
actual problem indicating the constraint name, the involved table and colum,
Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK_MasterT_DetailT'. The conflict occurred in database 'tempdb', table
'DetailT', column 'IdMasterT'.
The statement has been terminated.
no other way is allowed/possible...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Saturday, February 25, 2012
Returning row value before deletion
I want to return a value from a row before i delete it. Is this
possible to do in one operation something like...
SELECT t.columnVal FROM (DELETE FROM table t WHERE t.c1 = @.p1)
RETURN t.columnValCJ wrote:
> I want to return a value from a row before i delete it. Is this
> possible to do in one operation something like...
> SELECT t.columnVal FROM (DELETE FROM table t WHERE t.c1 = @.p1)
> RETURN t.columnVal
Not in one query but why don't you make a stored procedure that returns
the value(s) and deletes the rows?
Kind regards,
Stijn Verrept.|||Thanks Stijn-
This is running inside a stored proc. I have an output param that
needs to return a value from the record being deleted. I didn't want
to run both SELECT and DELETE against the table if there was a way of
capturing the record from the DELETE.
Regards,
-CJ|||in 2005 check out the OUTPUT clause.
William Stacey [MVP]
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1133142606.786674.84690@.f14g2000cwb.googlegroups.com...
>I want to return a value from a row before i delete it. Is this
> possible to do in one operation something like...
> SELECT t.columnVal FROM (DELETE FROM table t WHERE t.c1 = @.p1)
> RETURN t.columnVal
>|||Nope. Not in SQL 2000. Be sure to use WITH(UPDLOCK) or WITH(XLOCK) on the
SELECT statement that precedes the DELETE.
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1133143713.634416.313700@.o13g2000cwo.googlegroups.com...
> Thanks Stijn-
> This is running inside a stored proc. I have an output param that
> needs to return a value from the record being deleted. I didn't want
> to run both SELECT and DELETE against the table if there was a way of
> capturing the record from the DELETE.
> Regards,
> -CJ
>|||Many thanks!
So UPDLOCK on the SELECT will work if the SELECT and DELETE are wrapped
in a transaction and the DELETE cascades over sevreal tables?|||Brian
>Be sure to use WITH(UPDLOCK)
I think we should add WITH (UPDLOCK,HOLDLOCK) hint , shouldn't we?
I remember our last discussion sometime ago where you explained why we need
to use HOLDLOCK hint in addition to UPDLOCK.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:uTj93P88FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Nope. Not in SQL 2000. Be sure to use WITH(UPDLOCK) or WITH(XLOCK) on
> the SELECT statement that precedes the DELETE.
> "CJ" <Charles.Deisler@.gmail.com> wrote in message
> news:1133143713.634416.313700@.o13g2000cwo.googlegroups.com...
>|||Not prior to a DELETE. UPDLOCK is sufficient. UPDLOCK, HOLDLOCK is
necessary if there is a possibility of an INSERT or UPDATE on another
connection that could cause a PRIMARY KEY or UNIQUE constraint violation by
changing the value to one that is about to be INSERTed or UPDATEed.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eTAKC698FHA.2192@.TK2MSFTNGP14.phx.gbl...
> Brian
> I think we should add WITH (UPDLOCK,HOLDLOCK) hint , shouldn't we?
> I remember our last discussion sometime ago where you explained why we
> need to use HOLDLOCK hint in addition to UPDLOCK.
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:uTj93P88FHA.2364@.TK2MSFTNGP12.phx.gbl...
>|||If you have cascading referential actions, then you have to obtain update
locks all affected rows in all affected tables. I don't like cascading
referential actions. I think they are a tool for the lazy or the
incompetent and cause more problems then they're worth. They can cause
locking order confusion and can increase the probability of deadlocks. It
is not possible to determine with certainty which objects will be locked in
which order if cascading referential actions are active. It's better to
write the code manually in the stored procedure that performs the DELETE
(this is the preferred method) or to use a trigger (preferrably an INSTEAD
OF trigger) because then you have complete control over the order in which
locks are obtained, thus eliminating the main cause of deadlocks that can be
avoided. There still exists the possibility of deadlocks, due to threading
or a poorly generated execution plan, but deadlocks caused by accessing
objects in the wrong order can be completely eliminated.
You are correct that the SELECT(s) and the DELETE must be wrapped in a
transaction. In addition, if you don't use WITH(UPDLOCK), two separate
transactions can obtain and hold shared locks on the same row such that
neither can obtain an exclusive lock in order to perform the DELETE, thus
causing a deadlock.
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1133153077.282668.76100@.g14g2000cwa.googlegroups.com...
> Many thanks!
> So UPDLOCK on the SELECT will work if the SELECT and DELETE are wrapped
> in a transaction and the DELETE cascades over sevreal tables?
>|||Would the Ouput clause not fit here?USE AdventureWorks;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT deleted.*
WHERE DatabaseLogID = 7;
GO
William Stacey [MVP]
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1133142606.786674.84690@.f14g2000cwb.googlegroups.com...
>I want to return a value from a row before i delete it. Is this
> possible to do in one operation something like...
> SELECT t.columnVal FROM (DELETE FROM table t WHERE t.c1 = @.p1)
> RETURN t.columnVal
>
possible to do in one operation something like...
SELECT t.columnVal FROM (DELETE FROM table t WHERE t.c1 = @.p1)
RETURN t.columnValCJ wrote:
> I want to return a value from a row before i delete it. Is this
> possible to do in one operation something like...
> SELECT t.columnVal FROM (DELETE FROM table t WHERE t.c1 = @.p1)
> RETURN t.columnVal
Not in one query but why don't you make a stored procedure that returns
the value(s) and deletes the rows?
Kind regards,
Stijn Verrept.|||Thanks Stijn-
This is running inside a stored proc. I have an output param that
needs to return a value from the record being deleted. I didn't want
to run both SELECT and DELETE against the table if there was a way of
capturing the record from the DELETE.
Regards,
-CJ|||in 2005 check out the OUTPUT clause.
William Stacey [MVP]
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1133142606.786674.84690@.f14g2000cwb.googlegroups.com...
>I want to return a value from a row before i delete it. Is this
> possible to do in one operation something like...
> SELECT t.columnVal FROM (DELETE FROM table t WHERE t.c1 = @.p1)
> RETURN t.columnVal
>|||Nope. Not in SQL 2000. Be sure to use WITH(UPDLOCK) or WITH(XLOCK) on the
SELECT statement that precedes the DELETE.
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1133143713.634416.313700@.o13g2000cwo.googlegroups.com...
> Thanks Stijn-
> This is running inside a stored proc. I have an output param that
> needs to return a value from the record being deleted. I didn't want
> to run both SELECT and DELETE against the table if there was a way of
> capturing the record from the DELETE.
> Regards,
> -CJ
>|||Many thanks!
So UPDLOCK on the SELECT will work if the SELECT and DELETE are wrapped
in a transaction and the DELETE cascades over sevreal tables?|||Brian
>Be sure to use WITH(UPDLOCK)
I think we should add WITH (UPDLOCK,HOLDLOCK) hint , shouldn't we?
I remember our last discussion sometime ago where you explained why we need
to use HOLDLOCK hint in addition to UPDLOCK.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:uTj93P88FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Nope. Not in SQL 2000. Be sure to use WITH(UPDLOCK) or WITH(XLOCK) on
> the SELECT statement that precedes the DELETE.
> "CJ" <Charles.Deisler@.gmail.com> wrote in message
> news:1133143713.634416.313700@.o13g2000cwo.googlegroups.com...
>|||Not prior to a DELETE. UPDLOCK is sufficient. UPDLOCK, HOLDLOCK is
necessary if there is a possibility of an INSERT or UPDATE on another
connection that could cause a PRIMARY KEY or UNIQUE constraint violation by
changing the value to one that is about to be INSERTed or UPDATEed.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eTAKC698FHA.2192@.TK2MSFTNGP14.phx.gbl...
> Brian
> I think we should add WITH (UPDLOCK,HOLDLOCK) hint , shouldn't we?
> I remember our last discussion sometime ago where you explained why we
> need to use HOLDLOCK hint in addition to UPDLOCK.
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:uTj93P88FHA.2364@.TK2MSFTNGP12.phx.gbl...
>|||If you have cascading referential actions, then you have to obtain update
locks all affected rows in all affected tables. I don't like cascading
referential actions. I think they are a tool for the lazy or the
incompetent and cause more problems then they're worth. They can cause
locking order confusion and can increase the probability of deadlocks. It
is not possible to determine with certainty which objects will be locked in
which order if cascading referential actions are active. It's better to
write the code manually in the stored procedure that performs the DELETE
(this is the preferred method) or to use a trigger (preferrably an INSTEAD
OF trigger) because then you have complete control over the order in which
locks are obtained, thus eliminating the main cause of deadlocks that can be
avoided. There still exists the possibility of deadlocks, due to threading
or a poorly generated execution plan, but deadlocks caused by accessing
objects in the wrong order can be completely eliminated.
You are correct that the SELECT(s) and the DELETE must be wrapped in a
transaction. In addition, if you don't use WITH(UPDLOCK), two separate
transactions can obtain and hold shared locks on the same row such that
neither can obtain an exclusive lock in order to perform the DELETE, thus
causing a deadlock.
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1133153077.282668.76100@.g14g2000cwa.googlegroups.com...
> Many thanks!
> So UPDLOCK on the SELECT will work if the SELECT and DELETE are wrapped
> in a transaction and the DELETE cascades over sevreal tables?
>|||Would the Ouput clause not fit here?USE AdventureWorks;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT deleted.*
WHERE DatabaseLogID = 7;
GO
William Stacey [MVP]
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1133142606.786674.84690@.f14g2000cwb.googlegroups.com...
>I want to return a value from a row before i delete it. Is this
> possible to do in one operation something like...
> SELECT t.columnVal FROM (DELETE FROM table t WHERE t.c1 = @.p1)
> RETURN t.columnVal
>
Subscribe to:
Posts (Atom)