MSSQL: a limited account that allows you to read only certain types?

Question

How to create an MS SQL server user who is allowed to read specific views of a specific database?

Answer 1

Here is a script that creates a new user and gives him the ability to select only permissions for 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 may want to use 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 

Thus, if you have several users, for example, in Windows authentication mode, you can have many users, everyone can be granted access to this role. Thus, when adding / removing views, you do not need to touch each user.

Answer 2

The trick for this is to not give any explicit permissions to the public role. Each user irrevocably enters this role, so there is no way to prevent any database user from accessing the rights that you grant to the public.

In addition, you simply add the user to the appropriate database and provide only rights to objects that interest you.