Solved

Help understanding SQL Server

Posted on 2014-01-05
2
405 Views
Last Modified: 2014-01-06
I've created a SQL Login on a SQL 2005 server.
In the User Mapping tree, in the "Users mapped to this login" I select the databases that the SQL Login will be able to access. For every database that I tick the system automatically puts a tick in the PUBLIC checkbox under "database role membership for: database_name"

1. Does this mean that SQL Login now has Read-Only access to database_name?
2. I make no other adjustments. Why is it I am able to use a vendor's front-end application, together with that SQL login created earlier, to modify data in tables, etc?
0
Comment
Question by:JCTDD
2 Comments
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 250 total points
ID: 39758717
1. No. The public, by default at least, only provides connect permissions on the available endpoints, as well as SELECT/EXECUTE on various system views.

2. Could be any number of reasons. For example, the app could be using a different account to the one you think it is using for some operations. If using stored procedures, the procedure could be executing in a different context.

I'd suggest running a short trace on the database to double check which account it actually connecting to do the UPDATE/INSERTS.

If you want to double check what permissions the public role has, you can execute the following:
SELECT a.[name] + ' ' + v.[name] + ' ON ' + QuoteName(oo.[name]) 
+ '.' + QuoteName(o.[name]) + ' TO ' + QuoteName(u.[name])
   FROM dbo.sysprotects AS p
   JOIN master.dbo.spt_values AS a
      ON (a.number = p.protecttype
      AND 'T' = a.type)
   JOIN master.dbo.spt_values AS v
      ON (v.number = p.action
      AND 'T' = v.type)
   JOIN dbo.sysobjects AS o
      ON (o.id = p.id)
   JOIN dbo.sysusers AS oo
      ON (oo.uid = o.uid)
   JOIN dbo.sysusers AS u
      ON (u.uid = p.uid)
   WHERE  'public' = u.name

Open in new window

0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 250 total points
ID: 39759399
If you want to grant readonly permissions you will have to check the db_datareader role. For read/write db_datawriter role for full rights the db_owner role.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

816 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now