Grant permissions to SQL Server database users on specific objects
Need to allow a user access to only specific tables or other database objects, but not to the entire database? Get granular with the GRANT statement.
To apply granular permissions to a specific object, like a table, use the GRANT [permission] ON [object] TO [user] format.
To grant select permissions on a table to a database user:
USE AdventureWorks2012 GO GRANT SELECT ON OBJECT::[Person].[Password] TO [dba_test] GO
If your login has no User Mapping to any database user, you will receive an error like this:
Msg 15151, Level 16, State 1, Line 1
Cannot find the user ‘dba_test’, because it does not exist or you do not have permission.
… and a database user needs to be created for the login:
CREATE USER [dba_test] FOR LOGIN [BUILTIN\Users] GO
To grant execute permissions on a stored procedure to a database user:
USE AdventureWorks2012 GO GRANT EXECUTE ON OBJECT::dbo.uspGetBillOfMaterials TO [dba_test] GO