Table level disable or locking Script?

Question

Post reply

Table level disable or locking Script?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
December 26, 2011 at 9:17 pm

#250467

Hi,
Is it possible locking or disable particular database in table? Table should not an insert, update and delete operation.
could anyone suggestion me, please give me script if it is possible?
Thanks
ananda

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

offhand, i can think of three different ways so far.

ideally, you do it via permissions...if the only permissions you grant is SELECT, they cannot do anything else.

since DENY trumps other permissions, for the users who inadvertently inherit more permissions than you wanted, you could DENY INSERT,UPDATE,DELETE On YourTable TO PUBLIC

you could also create a trigger for insert,update and delete that raises an error and rollsback any transactions.

another idea is to create a separate filegroup, move the clustered index of the table to that filegroup, and then make that filegroup readonly.

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!

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Thanks for your reply...

I tried DENY option as following script but it is not hope..please give me script.

connect throu sysadmin login and created user and login.

USE [master]

GO

CREATE LOGIN [testuser] WITH PASSWORD=N'test@123', DEFAULT_DATABASE=[CEMS], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE [CEMS]

GO

CREATE USER [testuser] FOR LOGIN [testuser]

GO

USE [CEMS]

GO

ALTER USER [testuser] WITH DEFAULT_SCHEMA=[dbo]

GO

Grant connect SQL to [testuser]

go

-- upto here I can able login and connect with user name (testuser)

after that as below script does not working

GRANT DELETE, INSERT, UPDATE, SELECT ON testtable1 TO testuser

go

--Msg 15151, Level 16, State 1, Line 1

Cannot find the user 'testuser', because it does not exist or you do not have permission.

DENY SELECT, insert,update, delete ON testtable TO testuser

go

--Msg 15151, Level 16, State 1, Line 1

Cannot find the user 'testuser', because it does not exist or you do not have permission.

connect testuser,

CREATE TABLE testtable1 (c1 int NOT NULL,

c2 varchar(23) NOT NULL)

go

--Msg 262, Level 14, State 1, Line 3

CREATE TABLE permission denied in database 'CEMS'.

Thanks

ananda

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

Example:

USE AdventureWorks2008R2;

DENY SELECT ON OBJECT::Person.Address TO RosaQdM;

GO

For More: http://msdn.microsoft.com/en-us/library/ms173724.aspx

baabhu SSCertifiable Points: 6205 More actions · Answer 4

Ananda,

My immediate thought you may have mutiple servers connected through SSMS. Your create user scripts executed in different server and you are trying to deny the access in different server. Can you check whether you are performing end to end action in one server.

--- Babu

Lowell SSC Guru Points: 323518 More actions · Answer 5

try this example, one line at a time; it should help you see how no permissions, all permissions, and then deny for a specific user will work.

USE [SandBox]

GO

--I'm currently logged in as a superuser, sa or it's equivalent:

--create my test table to verify how permissions work.

CREATE TABLE testtable1 (c1 int NOT NULL,

c2 varchar(23) NOT NULL)

--i need a test user, don't really need a matching login for this test.

CREATE USER [ClarkKent] WITHOUT LOGIN;

ALTER USER [ClarkKent] WITH DEFAULT_SCHEMA=[dbo]

--test the users permissions right here by changing user context.

EXECUTE AS USER='ClarkKent'

--who am i i just to be sure:

SELECT SUSER_NAME(),USER_NAME()

--As Clark Kent, try to touch the forbidden table.

SELECT * FROM testtable1

/* wow...no surprise, access denied:

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object 'testtable1', database 'SandBox', schema 'dbo'.

*/

--change back into superman.

REVERT;

--as superman, lets give Clark access to a single table. note that CREATE or ALTER permissions are separate from SELECT/Insert etc permissions.

GRANT DELETE, INSERT, UPDATE, SELECT ON testtable1 TO [ClarkKent]

--put on my secret identity and try again

EXECUTE AS USER='ClarkKent'

SELECT * FROM testtable1

--well we see the data, !

DELETE FROM testtable1

--delete is allowed, but there was no rows anyway.

--change back into superman.

REVERT;

DENY insert,update, delete ON testtable1 TO [ClarkKent]

go

--put on my secret identity and try again

EXECUTE AS USER='ClarkKent'

SELECT * FROM testtable1

--well we see the data, !

DELETE FROM testtable1

/*

Msg 229, Level 14, State 5, Line 1

The DELETE permission was denied on the object 'testtable1', database 'SandBox', schema 'dbo'.

*/

REVERT;

DROP USER ClarkKent

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!

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 6

SQL Galaxy

One Orange Chip

Points: 29472

December 27, 2011 at 1:53 am

#1425605

Thanks Lowell, this is working fine ..