3

I have one user in my database, and I want to grant to him permission to read only one view, nothing more, no tables, nothing.

How can I do this?

Thanks a lot.

P.S.: I am using MS SQL Server 2005

JohnFx
  • 34,542
  • 18
  • 104
  • 162
rpf
  • 3,612
  • 10
  • 38
  • 47

3 Answers3

7
USE DB1;
GRANT SELECT ON OBJECT:: View1 TO JohnDoe;
GO

Refer to GRANT Object Permissions for other examples of granting permission

TStamper
  • 30,098
  • 10
  • 66
  • 73
  • I've done this. However I still get "SELECT permission was denied on the object" for an underlying object. The Login a is a member of the Public role and has Select permission to the view. I didn't think I needed to add permission to the underlying objects when granting permission to a view? – MrEdmundo Jun 24 '09 at 13:23
  • the link 'grant object permissions' state this and make sure you have admin rights also – TStamper Jun 24 '09 at 15:51
2

Try this:

GRANT SELECT ON whatever_your_view_is_called TO bob_or_whatever_his_name_is
Thomas Padron-McCarthy
  • 27,232
  • 8
  • 51
  • 75
  • Two questions: A new user does not have access to any object inside database? The other users have access to the new view? I want that only the new user have access to it. – rpf Apr 17 '09 at 15:19
  • 1
    By default, no one except the "database owner (dbo)" has access to anything, unless you as a dbo specifically grant access. – marc_s Apr 17 '09 at 15:32
0

This is fairly simple to do with the Enterprise Manager UI or using the SQL code posted by a few others.

One thing I'd like to add is that you want to AVOID doling out rights to the Public role on any object that you might want to later lock down because there every new user you add will automatically be a member of this role.

JohnFx
  • 34,542
  • 18
  • 104
  • 162