SqlCommand - preventing stored proc call in other databases

Question

When using SqlCommand to call a stored proc via RPC, it looks like it is possible to call a stored proc in a database other than the current database.

e.g. :

string storedProcName = "SomeOtherDatabase.dbo.SomeStoredProc";    
SqlCommand cmd = new SqlCommand(storedProcName);
cmd.CommandType = CommandType.StoredProcedure;

I'd like to make my DAL code more restrictive, by disallowing potential calls to another database. One way might be to check if there are two periods (dots) in storedProcName above, and if so, throw an exception. Any other ideas/approaches ?

Thanks.

Answer 1

There could be procedures in your database with dots in their name:

create procedure dbo.[Dots.In.Name]
as

As hallie says, you're better off with adequately securing the server (not granting any permissions in other databases for whatever account is being used by the SqlConnection).

Also, if you're dealing with a 2005 or 2008 database, there could be a synonym in your current database that actually references another database, or of course, any stored procedure in the current database may call a stored proc in another server/database.

So basically, take control of your database server (control what objects get created, and what permissions are granted), and write less code trying to cover cases which the server can now respond to adequately.

Answer 2

Typically such restrictions are configured on the server side.
SQL Server has a very extensive security model which allows it to provide very granular access by granting specific permission ("Read", "Write" and such) to specific "Principals" (Users or Groups) for specific database objects (Tables, Views, Stored procedures, schemas...)

Your suggestion of having the DAL layer apply some restriction to prevent the use of stored procedures outside of the default database associated with the ADO connection shows a pro-active attitude with regards to what is "sent to SQL". In general this is a valuable attitude (for example to prevent SQL injection), but in this case, I'd advise against it:
What if at some point in the future a particular "external" Stored Procedure is needed? Also, what if there are several stored procedures on the current database which should be off-limit to the application (because they are only for maintenance/data-loading/audit/whatever-else) ? By introducing some DAL level restrictions you may be setting up yourself for difficulties at a later time, and you may be led to believe that the access control is adequate which it probably isn't...