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

http://msdn.microsoft.com/en-us/library/ms187965.aspx

About these ads