Data TutorialsDatabases

Grant table-level permissions in SQL Server

Posted on March 30, 2020

Launch SQL Server Management Studio and connect with credentials that have been granted the ‘sa’ role.

Expand Security, right-click on Logins and select New Login.

Lauching SQL

Enter a descriptive Login name, select SQL Server authentication, and enter a secure password.  On the bottom of the page select the database Chartio will be connecting to as the Default database.

Select the database that you would like Chartio to conenct to as the Default database

Select the User Mapping tab, check the box next to the desired database, confirm that only ‘public’ is selected, and click OK.

Granting permissions

Click the New Query button and select the database you are connecting to Chartio.

Paste the following query into the query window and execute.  Replace “chartio_read_only” with the actual username of the user you created.

SELECT 'GRANT SELECT ON "' + TABLE_SCHEMA + '"."' + TABLE_NAME + '" TO "chartio_read_only"' FROM information_schema.tables

Execute the query and see the following

Select and copy the query results into the query window.

Remove any tables or views you do not wish the “chartio_read_only user” to have access to.  In this example I have removed the Invoice and InvoiceLine tables because they contain sensitive information.

GRANT SELECT ON "dbo"."Customer" TO "chartio_read_only"
GRANT SELECT ON "dbo"."Employee" TO "chartio_read_only"
GRANT SELECT ON "dbo"."Genre" TO "chartio_read_only"
GRANT SELECT ON "dbo"."MediaType" TO "chartio_read_only"
GRANT SELECT ON "dbo"."Playlist" TO "chartio_read_only"
GRANT SELECT ON "dbo"."PlaylistTrack" TO "chartio_read_only"
GRANT SELECT ON "dbo"."Track" TO "chartio_read_only"
GRANT SELECT ON "dbo"."Album" TO "chartio_read_only"
GRANT SELECT ON "dbo"."Artist" TO "chartio_read_only"

Execute the query.

Final result

Next you will need to grant view definition on the dbo schema. Copy the below query and execute it in SQL Server. Replace the username with the username you created earlier.

GRANT VIEW DEFINITION ON SCHEMA :: dbo TO chartio_read_only

You can now use these credentials to connect Chartio to your database with read-only permissions on only the tables you have specified.