Creating a SQL Server user with permission to read one view and nothing else - but he can see system views and procedures?

Question

My company hired a contractor to do a small project for us, for which he needs to select data from one single view in our main database (SQL Server 2005).

I wanted to create a locked-down SQL Server login for him, with permissions just to SELECT from "his" view...and nothing else.

So I created a new user on the server, and then I gave him permission just on this one view:

grant select on SpecialView to SpecialUser;

Basically, this works - he can't see any of our tables and stored procedures, neither any views except "his" one.

But:

• he can access all system views
• he can access all system stored procedures.

Apparently his permissions are automatically locked down (sys.objects shows only the objects on which he has permissions, *sp_who* shows only his own processes and so on).

So, my question is:

Is it possible to create an user without access to system views and stored procedures?
(and if yes, what am I doing wrong?)

Or is there some reason why even locked-down users need to have access to system views and stored procedures?

EDIT:
kevchadders, the user has no access to master, model or msdb - only to the database with the view he is supposed to see.

But, to make one thing clear: The system views/procs which the user can see are in the database where "his" view is...not in the master database. So I can't disable all his access, since he needs to select from one view in the same database.
The point is, even if I explicitly set permission only for the single view that he is supposed to see, why does he still see the system views/procs as well?

Answer 1

Right click on the user and select the User Mapping page.

From there have you tried disabling all his access from the master, model and msdb database?

I think he will be set up as public on all 3 of those so you could try removing the public role to see what effect it has on his login when running those system views/stored procs.

Or better still create a test login to experiment with it.

Answer 2

I don't think you can remove PUBLIC access for an User. ( From the User properties )

You Can DENY the permissions to the PUBLIC , from the database properties.

• Right-Click the DB
• Go to Properties
• Go to Permissions
• Change the Permissions for the GUEST role.

But You cant deny the 'CONTROL' permission on Master or Temp dbs and you cannot login to the server if you deny the SELECT on MASTER.

Answer 3

Use DENY VIEW DEFINITION. You cannot remove the user's ability to see the existence of the views and stored procedures, but you can remove the ability to view (most) of the contents in them.

deny view definition to smallperms_role 
go
sp_addrolemember 'smallperms_role ', 'smallperms_user'
go
sp_addrolemember 'db_datareader', 'smallperms_user'
go

For example: "EXEC sys.sp_databases" returns nothing, but executes. "SELECT * FROM INFORMATION_SCHEMA.TABLES" returns nothing, but does not return an error.