Menu
I think the answer is no but I'm looking to give someone access to a SQL Server database but I only really want them to have access to one table.
It's easy enough to limit someone to only access one database but have no idea if I can limit to a single table.
My thoughts were to create another database with a synonym to the other table and then limit the access to that database but I wondered if someone could think of a better way.
I'm also not convinced that it will work as I think there will be a conflict of permissions.
741
Using the UI you can use the User Mapping tab under the login, you can create the user with 'public' access only. Then you can go to the database and grant that user SELECT access to the particular table.
I want to deny all other users from modifying the table. And only user svc-eR to have write access. So you need to create a new ROLE in the database, DENY access to that table and then add all users to the ROLE . you'll still will need to add each new user to the role when creating them.
Yes.
exec sp_msforeachtable "DENY SELECT ON ? TO [username];"
GO
GRANT SELECT ON [schemaName].[tableName] to [username]
Go
While that works, you would probably be better off managing permissions using roles and AD groups.
135
The problem with looping through all tables and denying access would be if you add a new table.
The important thing is to not give the user 'db_datareader' access to the whole database. Using the UI you can use the User Mapping tab under the login, you can create the user with 'public' access only. Then you can go to the database and grant that user SELECT access to the particular table (by clicking the oddly named "Search" button under Securables tab).
This approach would work with script also of course.
16
GRANT SELECT ON [SchemaName].[TableName] to [UserName]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
