SQL DBA

Question

Except "sa" user any other logged into SQL(windows authentication) and accessing my database i need to lock particular table(if use select query for that table,it should not return any rows).

---

Thanks SABARINATHAN87

Answer 1

DENY SELECT, UPDATE, DELETE, INSERT on table_name TO freddie

Make sure that freddie is not a member of db_owner database role

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Uri, wouldn't you want to use DENY on the public role?  That would do it right? Of course, I don't think you can stop the database owning user or sa, but you can apparently members of the db_owner role...  Test script:

create database test
GO
use test
go
create user testNonDbo without login;
create user testDbo without login;
alter role db_owner add member testDbo;
GO
create table testRights
(
	testRightId int primary key
)
GO
grant select on testRights to testNonDBO;
GO

--works, part of db_owner group
execute as user='testNonDbo';
GO
select *
from   testRights
GO
revert;
GO

--works, granted rights explicitly
execute as user='testNonDbo';
GO
select *
from   testRights
GO
revert;
GO

--deny to the public group
deny select,insert,update,delete on testRights to public;
GO

--fails, due to deny 
execute as user='testNonDbo';
GO
select *
from   testRights
GO
revert;
GO

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'testRights', database 'test', schema 'dbo'.

--fails, even though member of db_owner role
execute as user='testDbo';
GO
select *
from   testRights
GO
revert;
GO

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'testRights', database 'test', schema 'dbo'.
GO


--the actual user who owns the database (and should be the schema/object) will not be blocked
execute as user='dbo';
GO
select *
from   testRights
GO
revert;
GO


--nor will the sa
execute as login='sa'
GO
select *
from   testRights
GO
revert;
GO

---

Louis

Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

Answer 3

I agree with you Louis . If I remember, well DENY permission on public role are applied to all users in the database... So if the only one user that the OP can choose  what script to use:-)    

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/