SQL Server - Roles - only access specific views

1

Hello

I have a simple task, but I am strugling with it for 2 days now.
There are 2 views I would like to expose to specific user (TestUser).
This TestUser shuould have minimum permissions possible (Only execute view).
Since I expect to add more users to access same 2 views, I would like to create a role
and add users to that role.

I have:

  • Createed new Database Role (TestRole)

  • Added 2 views to "Securables" list and Grant Select permissions to both views.

  • created new user TestUser and (Server role = public).

  • checked the database (where those 2 views are) under "UserMapping"

  • added TestUser to Role Member's list.

Test user can connect to sql server, but the problem is that TestUser cannot see Views section at all.

What am I missing here?

Thanks

2

Since you only granted select permissions - those users in that role will be able to select but will not be able to view.

If you add VIEW DEFINITION to the role then they would be able to see the view(s).

3

Thanks for your suggestion, but...

  • I have already tried to add VIEW DEFINITION, without success (I forgot to mention that in my first post - sorry)

I have set the same permission directly on newly created user "Test2" (no roles), and it worked (only select permission is checked). Not only that, but I have explicitly set DENY on VIEW DEFINITION for user Test2, since I don't want user to view definition.

If I understand VIEW DEFINITION is a permission to show details about view (columns and types)

Do you have any other ideas.

Thanks anyway

PS:
maybe someone creates a simple script to:
create, user/roles, simple view and assign permissions/role membership

4

Try this, change it as per your naming.

/This will create a login, I am using a SQL login here***/
USE [master]
GO
CREATE LOGIN [TestLogin] WITH PASSWORD=N'yourpass'
GO

/*****This will create a user in the database/
USE TestDB;
GO
CREATE USER [TestUser] FOR LOGIN [TestLogin] ;
GO

/This will create a ROLE in the database*****/
USE TestDB
GO
CREATE ROLE [TestRole]
GO

/******This will grant select permission to the role on the view dbo.view_1 ***********/
Grant select on dbo.view_1 to [TestRole]
GO

/*This will add user to the role/
USE TestDB
GO
ALTER ROLE [TestRole] ADD MEMBER [TestUser]
GO

5

Hello!

Thanks ahmeds08!
Your code actually worked. (See your modified code below)

I still haven't found out what have I done wrong.
I split the script in 3 parts.
Part 1 - Create database, tables and views
Part 2 - Create ROLE and GRANT/DENY permissions
Part 3 - Create LOGIN/USER and set ROLE's membership

Well, finally I found out that:

  • if I create login/user with script --> it works as expected
  • if I create login/user with SSMS (UI) -> I still cannot see views.

It must be that SSMS is creating something in the background.

Anyway.
Thanks for your help.

USE [master]
GO

/*******************************************************************************************/
/* PART 1 - Create DATABASE, TABLES, VIEWS  */
/*******************************************************************************************/
CREATE DATABASE MyTestDB
GO

-- Create Table
USE [MyTestDB]
GO
CREATE TABLE [dbo].[MyTable](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[DummyCol] [nvarchar](50) NOT NULL
)
GO

-- Insert Data
USE [MyTestDB]
GO
INSERT INTO MyTable VALUES('Value1')
GO
INSERT INTO MyTable VALUES('Value2')
GO
INSERT INTO MyTable VALUES('Value3')
GO

USE [MyTestDB]
GO
CREATE VIEW [dbo].[TestView1] AS SELECT * FROM MyTable
GO
CREATE VIEW [dbo].[TestView2] AS SELECT * FROM MyTable Where  DummyCol like '%2'
GO

/*******************************************************************************************/


/*******************************************************************************************/
/* PART 2 - Create Database ROLE, and GRANT/DENY permissions to the ROLE  */
/*******************************************************************************************/
USE [MyTestDB]
GO
CREATE ROLE [TestRole]
GO

/******This will grant select permission to the role on the view dbo.view_1 ***********/
GRANT select ON dbo.[TestView1] TO [TestRole]
GO
Grant select ON dbo.[TestView2] TO [TestRole]
GO
DENY VIEW DEFINITION ON dbo.[TestView1] TO [TestRole]
GO
DENY VIEW DEFINITION ON dbo.[TestView2] TO [TestRole]
GO

/*******************************************************************************************/


/*******************************************************************************************/
/* PART 3 - Create LOGIN/USER and ADD user to the ROLE  */
/*******************************************************************************************/

USE [master]
GO
CREATE LOGIN [TestLogin] WITH PASSWORD=N'ThisIsMyPassword1!'
GO

/*****This will create a user in the database */
USE [MyTestDB]
GO
CREATE USER [TestUser] FOR LOGIN [TestLogin];
GO

/*This will add user to the role */
USE [MyTestDB]
GO
ALTER ROLE [TestRole] ADD MEMBER [TestUser]
GO