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

About these ads

Posted on November 23, 2011, in SQL Server. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: