?
Solved

How to Allow users who are using a specific Application to modify database

Posted on 2013-12-28
4
Medium Priority
?
126 Views
Last Modified: 2014-07-08
hello
 we have sql server database "MyCompanyDB" "sql server 2012,Windows 2008R2"
there is a desktop Application and a website , both connected to this database "MyCompanyDB"
what i need is to prevent any Data modification "Update/Delete/add" UNLESS the end user use these Applications ... for example if a developer or user use a third party application that connect the database he cant modify the data even from management studio
how can i implement that
Note: we are using SQL Server Authentication as the server authentication
0
Comment
Question by:ali_alannah
[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
4 Comments
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39743423
this will deny access to any database unless the user is sysadmin or DB owner, you can then create read only logins dbowner for those you want to read db and read/write dbowner for application and website

USE MASTER
GO
DENY VIEW ANY DATABASE TO PUBLIC
GO

Open in new window

0
 
LVL 23

Expert Comment

by:Patrick Bogers
ID: 39743446
Hi

The application/website could login with a account that is dbowner for that database where you can give users a seperate readonly login (public) that is tied to this particulair database.
0
 
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 450 total points
ID: 39746848
hi

you should use a DDL trigger on the database.

read up : DDL TRIGGER
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 1050 total points
ID: 39747009
Have the application set a specific APP_NAME when it makes its connection to SQL Server.

Then add DML triggers on the table that reject any mods unless the APP_NAME matches what you specified in the valid application.

For example:


CREATE TRIGGER table1_trigger_verify_app
ON dbo.table1
AFTER DELETE, INSERT, UPDATE
AS
SET NOCOUNT ON
--if app name shows it's from good app, exit from trigger, which processes mods normally
IF APP_NAME() = 'Your_good_application_name_Bb78-E5D2a10597BB'
    RETURN
--missing or invalid app name, so rollback any/all mods user tried to make
RAISERROR('You cannot make any modifications to these tables outside the main application.', 16, 1)
ROLLBACK TRANSACTION
GO
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

765 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