Deny table Permission at Table Level:

Deny table Permission at Table Level:
1)Connect SQL Server through windows authentication at Sysadmin Level
2)Under Security Find Logins
Eg: Under my Security there are logins like Prasad,Rameh,Siva
3)Now i decided I want to give Deny Table permission to Siva named Login
4)Now Click on Siva Login Properties->Go to Server Roles->Give Public->Don’t give any other Server roles. And especially Don’t give sysadmin server role and at the same time he should not be the owner of that database(db_owner)
5)Next open Query Analyzer run the below Script.
use MCTS
GO
GRANT INSERT TO Siva
GO
use MCTS
GO
GRANT SELECT TO Siva
GO
use MCTS
GO
GRANT UPDATE TO Siva
GO

use MCTS
GO
DENY DELETE TO Siva--This Stops Dropping and Deleting
6) After running the query If siva next time login by using his credentials he can not run delete script.

----CREATING A ROLE AND ASSIGNING MEMBER TO IT.
Here i am creating a database role which will not allow users to take a specific database backup.
Here the database name is 'Test'
USE Test
GO
---Creating database role
CREATE ROLE [deny_backup]
GO
--Denying
DENY BACKUP DATABASE TO [deny_backup]
GO
DENY BACKUP LOG TO [deny_backup]
GO
--Adding member to database role
EXEC  sp_addrolemember N'deny_backup','ramesh'

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server