Solved

Sql Database permissions

Posted on 2013-11-05
6
297 Views
Last Modified: 2013-11-07
Hello,

I have a 3rd part application that allows a user to create a database to be used with their front end. Every year this user needs to create a few new databases. Id like to lock the user down to :

Create the database through the application provided to create the database.

Allow the user to only manage the database though this application. (Not use anything like Sql managment studio.)

And not give this user any kind of server level permissions. Can it be done?

I do have the option of migrating to sql 2012 if that would help with this.
0
Comment
Question by:BrownRJ
[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
  • 3
  • 2
6 Comments
 
LVL 15

Expert Comment

by:achaldave
ID: 39624736
Is the application uses any account to connect to database or passes the credentials of currently logged on user? If the application uses its own account you can remove user's permission from the database and configure permission for account used by the application. This will prevent user from accessing SQL server directly by using tools like SQL management studio.
0
 

Author Comment

by:BrownRJ
ID: 39625038
The application can use either windows authentication or sql. Currently it uses windows authentication.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39625630
You could have a DDL trigger that, upon db creation, changes the owner of the db.

You can have a logon trigger that would reject any attempt by that user to log onto a SQL instance using SSMS.

Does the user need to use SSMS to do other tasks on the same instance?
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

Author Comment

by:BrownRJ
ID: 39625696
Scott,

Id like the user to keep permissions as DBO for any database he creates. Do you have any suggestions on the logon trigger for the SSMS? They do not need to ever access the server this way.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39625786
If the user is dbo on the db, then he can do anything to that db, including delete it.

Unless the user changes it (not easy to do but probably possible), SSMS will come in with an APP_NAME() of:
'Microsoft SQL Server Management Studio - Query'

You can use that in the logon trigger to rollback (cancel) the login, something like this:



CREATE TRIGGER [Check_For_SSMS_Trigger]
ON ALL SERVER
AFTER LOGON
AS
IF ORIGINAL_LOGIN() IN (N'domain_name\restricted_user_name1') --, ...
AND APP_NAME() LIKE '%Management Studio%'
    ROLLBACK; --cancel/reject login, preventing specified user(s) from accessing SQL using SSMS
GO
0
 

Author Comment

by:BrownRJ
ID: 39630523
Scott,

That did the trick. But I notice it doesnt like user groups. Ill just create  a trigger for each person. Its not that many.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

689 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