permissionsSecuritysql server
The database I'm working with has about 50 tables in it.
A single user requires edit permissions on one of the tables, but should not have access to any other tables.
I know how to deny her the ability to edit any of the tables:
properties > permissions then denying her all the options.
Using this method, she can still view the tables.
Is there a way I can make it so she can only view the one table?
You can create a role and then grant / revoke permissions to it. Any user that is a part of the role will inherit the permissions.
Below is an example to get you started :
-- Create the database role
CREATE ROLE TableSelector AUTHORIZATION [dbo]
GO
---- Grant access rights to a specific schema in the databas
GRANT
SELECT
ON SCHEMA::dbo
TO TableSelector
GO
-- Add an existing user to the new role created
EXEC sp_addrolemember 'TableSelector', 'MyDBUser'
GO
-- Revoke access rights on a schema from a role
DENY ALTER -- you can customize here ...
ON SCHEMA::dbo
TO TableSelector
Please do this after denying access to other database .. i think newely created user's default database is MASTER you need to give him newley created db as default database. you can do this by following steps
1) go to SECURITY >> LOGINS >> Select USER >> RIGHT CLICK on it >> Select PROPERTIES >> Select you newley created Database in DEFAULT DATABASE
Please see below screenshot for the reference
Best Answer
It'll involve 1) removing the user from any database fixed roles like
db_datareader(onlypublicwill remain), 2) creating a custom role in the DB, 3) granting the new role specific permissions on the one table, and 4) adding the user as a member of the new role. You'll also want to make surepublichas no grants to user objects as all users in a DB are a part of thepublicrole.