Solved

Sql Database permissions

Posted on 2013-11-05
6
292 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
  • 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:ScottPletcher
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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:
ScottPletcher 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Complex SQL script 1 31
Calculate values in an array 18 33
T-SQL--Two Different Totals Even with the Same Concept 6 45
SQL VIEW 7 0
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

914 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

15 Experts available now in Live!

Get 1:1 Help Now