Sql Database permissions
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.
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.
achaldave
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.
ASKER
The application can use either windows authentication or sql. Currently it uses windows authentication.
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?
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?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
That did the trick. But I notice it doesnt like user groups. Ill just create a trigger for each person. Its not that many.