Creating a user and granting table level permissions in SQL Server

Data Sources

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

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


Related Data Sources Help Articles

See more