Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-09-24 : 08:49:30
|
| Roberta writes "Here is my problem: We want to implement Application role security so that users don't have access to do queries through excel or other query reporting tools.The problem that we are having is that our application uses crystal reports. We found that with Crystal reports we can't use the application role to connect to the database. We thought no problem all of our reports use store procedures. The only problem is that the stored procedures were written using dynamic SQL. Here are my questions.1.) I have read that since I am using dynamic sql in the stored procedure I have to give permissions to the tables I am using in the store procedure. I am assuming that this is true. I was wondering if anyone one has found a way to do this though?2.) The other question is that if we have to change all our stored procedures is there a way to limit the records by a value in a field only if the user entered something without having to use LIKE or have the query written X amount of ways using the if statement? Example: Currently we build a string containing a SQL statement. If the user provides a value to limit the records by we add AND column_Name = @Variable_Name to the SQL string. The only ways I can think to do this without using dynamic are 1.) If the user does not provide a value set the variable to '%' then do SELECT column_Name FROM table_Name WHERE column_name LIKE @Variable_Name. This options could really slow down our queries. 2.)If @Variable_Name = '' Then SELECT column_Name from table_name ELSE SELECT column_name from table_name WHERE column_name = @Variable_Name. This would be kind of time consuming and difficult because some of the queries are rather complex and have multiple parameters where if a user enters something they limit the data to that value it they don't then the data is not limited.I hope you can give me some ideas. We are using SQL 2000Thanks." |
|
|
|
|
|
Application Role and Dynamic SQL