1

We have a SQL Server (Standard) database with a user whom we are attempting to restrict permissions to only CRUD Operations to certain tables (CREATE, SELECT, UPDATE, DELETE) and only allow connect permissions. However, the user still has access to all of the system functions. While some of these are required, we want to restrict users from using some of these functions. For example; host_id(), Db_Id(), etc.

We have the following configurations so far:

At Server Level

Server Login - ServerLogin1

  • Specific SQL Server Login
  • Roles: ServerRole1 and public
  • User mapping: to Database1 as DbUser1 with memberships to DbRole1 and public
  • Securables:: TSQL Default TCP - GRANT connect

Server Role - ServerRole1

  • Securables: TSQL Default TCP - GRANT connect
  • Securables: Server - GRANT connect SQL

At Database Level

Database Role - DbRole1

  • Securables: sysb schema - DENY - Create Sequence, Delete, Execute, Insert, References, Select, Update, View Change Tracking, View Definition
  • Securables: All sys views - DEBY - Select
  • Securables: All our DB tables - GRANT - Delete, Insert, Select, Update

Database User - DbUser1

  • Only has DbRole1 assigned
  • Securables: on sys schema - DENY - Delete, Insert, Select, Update

However, we can't seem to find a way to restrict the user from using some of the system functions. How do we deny these permissions?

Improve this question
3
  • 3
    What could possibly be the security issue with host_id() or Db_Id()? And you shouldn't need to DENY, just create a role that has only the GRANT you need and put the user in that
    – Charlieface
    Nov 29, 2021 at 23:00
  • We are using this SQL User to connect to the database via an application and at the moment we are unable to restrict some of the SQL Server built-in functions via the application. We are working on this, but need an interim solution. We have created a specific role, but when creating a user, the user automatically gets the public role and the public role allows built-in functions like host_id() and Db_Id(()
    – hoz
    Nov 30, 2021 at 10:56
  • I still don't get why you would ever want to do this (so what if user accesses their own host_id or db_id? They know that already.) But have you tried DENY VIEW SERVER STATE TO DBRole1 and DENY VIEW DATABASE STATE TO DBRole1
    – Charlieface
    Nov 30, 2021 at 11:20

1 Answer 1

Reset to default
1

It is not possible to restrict the use of built-in functions. There was a failed attempt at implementing a system to handle such restrictions in SQL Server 2019 (named "feature restrictions"), but fortunately that never made it out of beta / CTP (fortunate because it could not accomplish its goal of preventing certain types of activity that could be accomplished via different built-in functions and/or language constructs).

Nor is it even possible to restrict a session from seeing its own data. Meaning, not only can you not restrict DB_ID(), you can't even prevent rows that the login has access to from sys.databases. The documentation for DB_ID even states:

Permissions

... The database to which the caller connects will always appear in sys.databases.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.