Solved

Create  a ReadOnly User  - in sql code

Posted on 2015-02-18
1
56 Views
Last Modified: 2015-02-21
Hi.
What is the syntax for creating a new USER (login?) that only has READONLY access to a single table.
For example how do I create a NEW user named  'BOB'  who ONLY has READONLY access to the Employee table in Northwind?
thx in advance
0
Comment
Question by:JElster
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40616335
login is not the same as user ...
login is on server level, and the creation sql is documented:
https://msdn.microsoft.com/en-us/library/ms189751.aspx

the user is the mapping of the login in a given database (means for each db in the instance, you would need to map the login again)
that syntax is also documented:
https://msdn.microsoft.com/en-us/library/ms173463.aspx

by default, the user can only read the database structure (table names etc), but no data.
to grant the "read only", you just grant the SELECT permission:
https://msdn.microsoft.com/en-us/library/ms187965.aspx
USE Northwind
go
GRANT SELECT ON dbo.Employee TO BOBO

Open in new window

0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question