Sql Database permissions


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.
Who is Participating?
Scott PletcherConnect With a Mentor Senior DBACommented:
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:

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
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.
BrownRJAuthor Commented:
The application can use either windows authentication or sql. Currently it uses windows authentication.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Scott PletcherSenior DBACommented:
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?
BrownRJAuthor Commented:

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.
BrownRJAuthor Commented:

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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.