One issue that will come up from time to time when working with Dynamics SL using Windows Authentication is being able to access data across databases. Windows Authentication is specifically designed to “prevent” that functionality. In other words Windows Authentication is in place to provide strong security in spite of the ease that an integrated login brings. There is a way around the problem of getting at the data you need while obeying good security practices.
The first step is to set up an additional SQL user that you can use to assist with the need to get to the additional database. Grant that user the rights needed to make the queries work. Next, add a modified stored procedure to include a “With Execute” as clause. That is pretty much the trick to it, but some specifics are likely to be helpful.
The first step is setting up the SQL user to be used for these queries. There are actually multiple steps here (nothing is ever as easy as it seems). The first one is to create the login. This can be done manually or using this query: Create Login <user> with Password = ‘<password>’; – replace the <user> with the name you want and <password> with the corresponding password.
Now you can create users for that login in the databases it will need to access. For each database, run this query (again it could be done manually instead): Create User <user>; – note that the <user> is the same as the first time and this query needs to be run in the database you want to create the user in (using Use <database>; prior to that query will enforce this).
Once the user has been created for a database, you can grant the rights that will be needed. Exactly what those rights need to be depend on what you need to do (and more rights can always be added later). The query to grant those rights will again need to be run in the correct database (or can, again, be done manually): GRANT <right> ON <DB Object> TO [<user>] – in this query <right> is the right that you want to grant (Control, Select, Execute, etc. It will vary based on need and type of object), <DB Object> is the name of the object the user (yes, the same one again) needs rights to. Generally, the list of objects will consist of tables and/or views, but it is possible that stored procedures might be needed as well.
It is also possible to add this user to a role (and may sometimes be necessary). That can be done with this query: exec sp_addrolemember ‘<role>’, ‘<user>’ – where <role> is the role you need the user to have.
The security is now set up and the user is ready for use. On to the stored procedure (also known as step two). You have a stored procedure defined that will return the results you want, if only security would let the program execute it (yes, it is presumptive, but, if you don’t have one, how do you know you have a problem and how will you know when it is resolved?).
Adding a With Execute as clause to the stored procedure will cause it to run using specified user instead of the one SL normally uses. An example of that change (the change is the second line for those having problems seeing the obvious):
CREATE PROCEDURE <procedure name> <parameter list>
WITH EXECUTE AS ‘<user>’ AS
<definition of procedure>
Where <procedure name> is the name of your stored procedure, <parameter list> is the list of parameters needed (same as always there), and <user> is the user we labored so hard on earlier (<definition of procedure> is the list of queries run by the procedure).
You should now be able to use the newly defined stored procedure in your SL application. There are a couple of additional points that should be mentioned. I have found that sometimes the toolkit gets cranky when you are using complex stored procedures (containing more than a single query, especially if using aggregates, and even when you are only referencing the active database). Frequently this can be fixed by declaring the cursor used to run that stored procedure with a SQLFastReadOnly flag.
That change will prevent you from using that cursor for updates to the database, but you can use a different cursor for performing any needed database updates – the complex query wouldn’t work for updates anyhow.
A challenge arises infrequently when the system suddenly decides that the user no longer has the rights needed for executing the queries in the stored procedure. Generally, this only occurs when a database has been restored into another (in creating a test database from the live, for example). The only solution that I have found for this situation is to drop the stored procedures that contain a With Execute As <user> clause, delete the user from all databases, and delete the login.
Once the user has been cleaned out, you can recreate the login, users and stored procedures using the same process as before (which is a very good reason for using queries to set up security instead of doing the steps manually). The stored procedure will now work again.This should allow you to get to the data in both databases in an easy way.
