MSSQL: Limited account that's only allowed to read certain views?

Question

How do I create a MS SQL server user that only allowed to read certain views of a particular database?

Answer 1

Here's a script that creates a new user and gives him select only permissions on a specific view.

USE [master]
GO
CREATE LOGIN [LimitedUser] WITH PASSWORD=N'testPass', 
           DEFAULT_DATABASE=[master], 
           CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [TestDB]
GO
CREATE USER [LimitedUser] FOR LOGIN [LimitedUser]
GO
use [TestDB]
GO
GRANT SELECT ON [dbo].[myView] TO [LimitedUser]
GO

Edit

Instead of doing this for a specific user you might want to consider using Roles instead.

USE [TestDB]
GO
CREATE ROLE [LimitedRole]
GO
GRANT SELECT ON [dbo].[TestView] TO [LimitedRole]
GO
EXEC sp_addrolemember N'LimitedRole', N'LimitedUser'
GO

This way if you have multiple users for example in a Windows Authenitcation Mode you might have many users, all can be granted access to this role. This way as you add / remove views you don't have to touch every user.

Answer 2

The trick to doing this is to not give any explicit permissions to the Public role. Every user is irrevocably a member of that role so there is no way to prevent any user of the DB from having any rights you give to Public.

Aside from that, you just add the user to the appropriate DB and only give them rights to the objects you are interested in.