This article is half-done without your Comment! *** Please share your thoughts via Comment ***
How to revoke all permissions of a user and grant only one table to that user in SQL Server?
If you are SQL DBA and planning for an interview, you should prepare for database security related interview questions.
I already published, please check the below link.
As a SQL DBA, operating with database permissions is a routine work like giving permission to ad-hoc users and modifying whenever it is required.
Last year, I asked this question to a DBA, and I got an answer like “Remove the user and Create a new user with specific table permission.”
What do you think? Is this correct answer? – Partially Yes…Before removing a user, you should verify the created objects by that user and create a new user with the same old name.
But without doing this all, the answer can be the below script:
Stop access to all tables for a user:
|
1 2 |
EXEC sp_msforeachtable "DENY ALL ON '?' TO [User_Name];" GO |
Grant specific table permission:
|
1 2 |
GRANT ALL ON [Schema_Name].[Table_Name] TO [User_Name] GO |
