We have an application that we need to restrict access to. The application will be using login TestApp. I've been testing this under Sql Server Management Studio v 17.9.1 and we are using SQL Server 2016. This Login will have access to all tables Store procs, views , functions and triggers within its own schema Test in Database: Testing, and only to certain tables from dbo Schema that we specify in the permissions of the TestAPP user. I've used the following script to achieve that. The problem we are having is that the Login can access all System Views and Store Procs and we don't want this level of access. This login Shouldn't be able to do those under schema TEST.
-
Create, drop, alter tables from Test schema
-
Create, alter, drop views from Test schema
-
create , alter , drop store procs from Test Schema.
We are trying to use Roles so that we don't have to specify each and every sys object that we want to deny access to.
I've created the Role [db_TestExecutor] and attached the role to the user. and then gave specific permissions for the dbo tables but still they can see all sys objects.
Used SQL code Below:
USE [Testing]
GO
CREATE SCHEMA [Test] AUTHORIZATION [dbo]
GO
CREATE ROLE [db_TestExecutor]
GO
GRANT EXECUTE ON SCHEMA::Test TO [db_TestExecutor]
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Test TO [db_TestExecutor]
USE [mASTER]
GO
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE [name] = 'TestApp')
BEGIN
CREATE LOGIN TestApp WITH PASSWORD=N'Password123', DEFAULT_DATABASE=[tempdb], DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
END
USE [Testing]
GO
CREATE USER [TestApp] FOR LOGIN [TestApp]
GO
ALTER ROLE [db_TestExecutor] ADD MEMBER [TestApp]
GO
GRANT INSERT ON [dbo].[tblOrders] TO [TestApp]
GO
GRANT SELECT ON [dbo].[tblOrders] TO [TestApp]
GO
GRANT UPDATE ON [dbo].[tblOrders] TO [TestApp]
GO
GRANT SELECT ON [dbo].[test] TO [TestApp]
I expect the TestApp login to be able to see only objects from Test schema and only certain dbo tables.