asked on

Access to SQL Database to run queries.

Access to SQL Database to run queries.

I would like to know how to give permissions to a specific user to access specific database and be able to run all queries.
I also would like to know which tool the user will use to run the queries on.

Thank you

ASKER CERTIFIED SOLUTION

Peter Chan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lcohan

Aside the many free tools that exists the users can use yje native sqlcmd or SSMS to run queries against a SQL Sever database and give them only the the "db_datareader" permissions as this way they can't perform any DDL/DML changes in that database. Relevant links below:

https://www.microsoft.com/en-us/download/details.aspx?id=53591
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

For instance the tsql below would grant "read-only" access to all tables/views from the YourDBnameHere database to the UserName login/user. ideally if users have AD logins part of a AD Group you will need to add that AD group to SQL and grant the group level access so you don't need to run commands like below for each individual user.

USE [YourDBnameHere]
GO
CREATE USER [UserName] FOR LOGIN [UserName]
GO
ALTER USER [UserName] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [UserName]
GO

Open in new window

jskfan

ASKER

Thank you Guys!!

I will test later when I get a chance