Skip to main content

Removing public/guest permissions

By default, in SQL Server, most objects have public permissions granted. If you remove the default public and guest permissions from your database, for example in SQL server 2016 and after, you must ensure the following steps are performed to ensure Bravura Security Fabric operates correctly:

  1. Start Microsoft SQL Server Management Studio.

  2. Connect to the server as a system administrator (sysadmin role).

    For example, to connect to the server using the sa account, set:

    Server type: Database Engine

    Server name: <host name or IP address> \ <instance>

    Authentication: SQL authentication

    Login: sa

    Password <password for sa>

  3. Click Connect.

  4. Create a new user:

    1. In the Object Explorer (left) pane, expand Databases > SystemDatabases > master > Security , right-click Users, then select New User….

    2. Type the User name.

    3. Select Login name created in Creating a dedicated database, user, and schema .

    4. Select Default schema (for example, sys).

    5. Select Securables to search and grant select permission on schemas sys and INFORMATION_SCHEMA.

    6. Click OK.

  5. Create a new database role for sp_describe_first_result_set:

    1. In the Object Explorer (left) pane, expand Databases > System Databases > master > Security > Roles, right-click Database Roles, then select New Database Role….

    2. Type the Role name.

    3. Add user created in previous step to Role Members.

    4. Select Securables page, and click Search.

    5. Select sp_describe_first_result_set (Extended Stored Procedures).

    6. Grant Execute permission, and click OK.

  6. Repeat last step to create a new database role for sp_executesql.

  7. For upgrade or migration, repeat to create a new database role for sp_rename.

  8. For upgrade or migration, ensure your login user can connect:

    1. In the Object Explorer (left) pane, expand Security > Logins, and select your login user.

    2. Select Securables page, and click Search.

    3. Select your server.

    4. Under the Permissions for <server> check the following permissions:

      • Connect SQL

      • Control server

      • Create any database

      • Create availability group

      • Create DDL event notification

      • Create endpoint

      • Create server role

      • Create trace event notification

      • External access assembly

      • View any definition

      • View server state

    5. Click OK.

Advanced configuration

If you require that the dedicated user works with fewer permissions, you can do one of the following:

  • Modify installation options so that Bravura Security Fabric installs to a named schema owned by a different user.

    The ”schema install user” requires all of the roles described in the above procedure.

  • Modify installation options so that Bravura Security Fabric does not install the schema, and instead uses a schema already set up by your database administrator.

  • Remove extra permissions from the dedicated user after Bravura Security Fabric is installed.

In the above cases, the dedicated user requires at least the db_datareader and db_datawriter database roles, as well as permissions to the schema objects. This includes: EXECUTE permission on the stored procedures, VIEW DEFINITION permission on the stored procedures and views. To learn how to grant permissions to schema objects, contact your database administrator or refer to your database documentation.

See Pre-configured database server settings for more information about advanced installation options.

In this section: