I have a production database with crucial business information. Meanwhile, my company is involved in a project with some other company and we have to exchange some information on a regular basis. How secure is it if I let the other company's IT department execute stored procedures on our database server? They would have to know the database name, address, procedure name and of course I could set them a new login. My worries are, are there "easy" ways of bypassing security and accessing other data in the database?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
I would first question why they need direct access to the database. You might ask your manager or legal department if the security policy for the company allows granting this type of access. Is it really needed if they are just going to execute a stored procedure on a regular basis.
As you stated this is going to be on a regular basis I would setup an SSIS package to export the information to a file. Then either have the package email the file (if not to large) or put it on a UNC directory for them to come and download themselves. If they want to determine when the data is pulled down setup a script for them to execute outside of SQL Server that simply runs the package.
I have been to in this situations before where for a small projects (yet for good amount of period) we need to work with some out sourced team and we also need to make sure the client data is safe. for every day work we can not keep an eye on who is accessing what. on the other side we also want to make sure they can access information they really need as smoothly as possible. in this cases, I would suggest to create specific USER_ROLE and assign appropriate permissions to that role. not only that also Deny the Other permissions explicitly. Add the specific/needed user from that team as member of this role. of course you will make sure that the Role doesn't have any sysadmin/serveradmin/dbowner/securityAdmin/BackupCreator etc... kind of permissions.
a single person can manage the adding or updating the permissions on db objects as needed/requested.
Granting/revoking/Denying permissions on different objects can be manged by automated scripts to reduce daily overhead.
by this way at least your team will be aware of what has been given access to at any point in time.