July 7, 2016 at 10:10 am
Hi,
1. I try to restrict one table (Department) on database "AdventureWorks' at object level using user a/c.
2. But it's apply to all tables either restrict or access.
3. I want to restrict only any one table and remaining are accessible mode.
Pls any body help on this. i want step by step instructions (Through GUI)....
Pls provide your valuable suggestions... thanks in advance....
July 7, 2016 at 10:41 am
Assumptions:
Server Level Logon is already a user to the database in question.
You want to apply a deny (restrict) user to a specific table in a database.
User in database is NOT in the dbo or db_owner roles (wont work/cant restrict their access)
Steps:
Using SQL Server Management Studio
Connect to the server in question:
At the database in question:
Open (drilldown) on Tables
Right Click on the Table in question
Left click on Properties (bottom of the right click menu)
Select Permissions from the Select a page menu (Near top left of the Table properties window)
Click the Search... button
Click the Browse... button
Place a check in the checkbox next to the user in question you want to restrict access for
Click Ok
Click Ok
Check the boxes in the Deny column for your restrictive criteria:(Select,Update,Insert,View Definition etc...)
Click Ok
July 7, 2016 at 10:29 pm
Thanks for respond...
Created SQL Login
1.I have provide "Public" & "Securityadmin" at server level privileges.
2.I have provide “db_accessadmin” & “db_datareader” & “db_securityadmin” & “public” at database level privileges.
3.Then go with “SQL Login”.
4.Expand database-? then expand tables -? and select table & select properties.
5.In “select page” popup-window ? go to “Permissions” tab ? select “search” then select “Browse”. ? here select “User” ? OK ? OK
6.Here I choose “Select” with “deny” ? option. ? OK
7.In query window “USE AdventureWorks2012” GO
8.Then select * from HumanResources.Department
9.But here display results…
10.Not comes error message…
What are the options need to select at "Server level & DB Level", for this security..? why it is not working still..?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply