manage the stored procedure permissions....

hello all....can anyone tell me how can i manage the stored procedure permissions in SQL Server Express. Untill now i have developed in SQL Server 2000 and there i used SQL Enterprise Manager for this problem..in SQL Express i can't handle it...Manny thanks.....

hi,

for a graphic management tool, you can have a look at the Microsoft free provided one, you can get for free at http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

regards

|||

Thanks Andrea....but i have allready installed this tool...

in the mean time ....i've find the solution using that tool......go in the DB tree : Security-Users (choose the user that i want to add permisions) -Properties- Securables- here i add the objects and grant the permisions....

Making our SQL Server 2000 application ready for SQL Server 2005

We have several applications that were developed using SQL Server 2000. Most of our customers run MSDE, some the full SQL Server 2000. We are in the process of making sure our applications will work properly in SQL Server 2005 / SQL Express.

I have a few questions regarding this issue:-

1. Should I set the compatibility level to 80 for our databases or should I aim to make our applications work the 90 compatibility level?

I've run the Upgrade Advisor against our databases and this has not flagged up any issues. Does this mean the databases are compliant with the 90 compatibility level.

Of course this hasn't verified the queries built into my VB application so presumably I need to test our applications thoroughly before we let our customers run them on SQL Server 2005? (Note: Our newest applications do approx. 95% of the queries through stored procedures, however our older applications are more like 50% - this I presume potentially means there are a lot of queries hard coded into our code that could potentially not work and we need to test the applications directly against SQL Server 2005?)

2. If the compatibility level is set to 80, does the guarantee the database functions EXACTLY the same as SQL Server 2000? Could anything not work that did work in SQL Server 2000? Note: I know the SQL Agent Service is not available in SQL Express - this is not my main concern - i'm more on about general stuff like queries etc here.

3. If the compatibility level is set to 80 do you still get some SQL Server 2005 benefits - e.g. running on a faster engine giving me performance benefits or does the database need to be set to compatibility level 90 to receive any performance benefits?

4. If you restore a SQL Server 2000 database into SQL Server 2005, does it automatically update the database so it no longer will work in SQL Server 2000? Can I for instance have the database set to compatibility level 80 with nothing upgraded and then restore the database back on a SQL Server 2000 machine. I don't believe this is possible, but need to check.
Thanks in advance,
Chris

1 The upgrade advisor checks that your SQL server config is okay for upgrade, not the database itself;
Analysis Services Upgrade Issues|||

Mulhall wrote:

2&3 Check out this link and "Behavioral Differences Between Earlier Compatibility Levels and Level 90"
http://msdn2.microsoft.com/en-us/library/ms178653.aspx

In this link it states..

"The sp_dbcmptlevel stored procedure affects behaviors only for the specified database, not for the entire server. sp_dbcmptlevel provides only partial backward compatibility with earlier versions of SQL Server. Use sp_dbcmptlevel as an interim migration aid to work around version differences in the behaviors that are controlled by the relevant compatibility level setting. If existing SQL Server applications are affected by behavioral differences in SQL Server 2005, convert the application to work properly. Then use sp_dbcmptlevel to change the compatibility level to 90."

The above text implies compatibility levels should be used just as an aid until our application is fixed. I've read a lot of the upgrade advisor help file and can't see anything in there that our application would have trouble with.

Therefore, If i've run the upgrade advisor and I have no issues (and I can't see anything in the help file that would cause us problems) then presumably the next step is to restore our databases in SQL Server 2005 and change the compatibility level to 90 and start testing our applications directly?

|||There are noguarantees! :)
In your place I would check whether I am aware of any points detailed in the above link that would impede your applications functionality or performance.
I'd then restore the database in SQL 2005 and start testing, if I hit issues that might be compability related, then I'd drop the compatability level to try to resolve them.sql

Making a copy of an SQL 7 database into an MSDE environment

We have developed an application for Small Business
Server (SQL) on a system that is running MSDE. All
programming and testing was conducted on a remote,
standalone system, and when completed, we were able to
move appropriate copies to the LAN via an external 80GB
Western Digital USB attached portable drive.
Is it possible to copy one or more or the SQL databases
(all tables) from the SBS SQL server to an MSDE system?
(ie a network attached laptop).
I would appreciate your assistance and comments.
Regards...Dave K.
hi Dave,
"dkalling" <dkalling@.cnx2.com> ha scritto nel messaggio
news:04c001c4eddc$579de490$a401280a@.phx.gbl
> We have developed an application for Small Business
> Server (SQL) on a system that is running MSDE. All
> programming and testing was conducted on a remote,
> standalone system, and when completed, we were able to
> move appropriate copies to the LAN via an external 80GB
> Western Digital USB attached portable drive.
> Is it possible to copy one or more or the SQL databases
> (all tables) from the SBS SQL server to an MSDE system?
> (ie a network attached laptop).
> I would appreciate your assistance and comments.
> Regards...Dave K.
as you can't use DTS to manage MSDE instances (and more you say that your
SQL Server is a version 7.0 server), you have to rely on backup/restore
and/or sp_detach_db/sp_attach_db solutions
please keep in mind you will probably experiencing orphaned users problems
after that operation(s), you can resolve using system stored procedure
sp_change_users_login
(http://www.sqlservercentral.com/colu...okenlogins.asp)
you can move users databases this way only from 7.0 version to 2000 version,
and not vice versa
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

Major Problem!...Multiple user connection problem in reporting service 2000 with SOAP

Hi Everyone,

I am having a big problem here.

I am using Reporting Service 2000 and Visual Studio 2005. I have developed a custom web control which communicates with reporting web service and displays report. I pass username, password and domain information as network credential for communicating with report server (report server is remote). This is a web application in which users can see reports on the web page which uses my custom web control.

Everything works fine when there is single user. But when another user logs onto website and tries to access same or different report it seems like connection of the first user gets lost. And everything crashes for first user and for second user it works fine.

In fact if second user is accessing the same report then first user can see second user's report information which is no at all acceptable. I am sure that there should be some way so that multiple users can communicate with reporting server programmatically at the same time. Microsoft wouldn't make reporting web service so that only one user can access report at a time, would they?

Can anyone help me with this problem? Any help will be greatly appreciated.

Sounds like your users are being identified as the same person which would suggest annonymous access is being used or your web control is passing the same credentials for both users. I'm not sure how you've coded it or whether you are doing any caching or storing session state somewhere.

You have to narrow down what is at fault here. I would suggest the following:

Set your report server and report manager virtual directories to integrated or basic authentication and have 2 separate users access report manager at the same time|||

Thanks Adam for quick reply.

I don't have anonymous access enabled. I am using integrated windows authentication. Earlier i was trying to authenticate all users with one administartor credentials to communicate with Reporting web service. But then i changed my code so that it takes individual user's credentials. But this didn't make any difference.

I read in one reporting service book that Report server overwrites SessionID member after each call to the Render method. Therefore, two subsequent report requests will share same sessions only if they ask for the same report (assuming that the parameter set is same)

For example, let's say you run report A, then report B, and then report A again. When report B is rendered, its session identifier will overwrite the previous session identifier, which means that you will lose report A's session identifier. When report A is run again, even if the parameter set is the same, its execution will create a new report session and IsNewExecution will return true.

So i think this overwriting of SessionID by report server is causing the problem. But i don't know yet how to program so that it maintains proper SessionID for each user and hence doesn't crash for any user.

If anyone has any idea for solving this problem then please let me know. Thanks in advance.

|||Figured out. It was bug in my code. Accidentlly i had declared Parameter property as static in my code and because of that one user's report was using other user's property and which was making program crash. Thanks for reply though.

Major Multiple connection problem RS2000 with SOAP

Hi Everyone,

I am having a big problem here.

I am using Reporting Service 2000 and Visual Studio 2005. I have developed a custom web control which communicates with reporting web service and displays report. I pass username, password and domain information as network credential for communicating with report server (report server is remote). This is a web application in which users can see reports on the web page which uses my custom web control.

Everything works fine when there is single user. But when another user logs onto website and tries to access same or different report it seems like connection of the first user gets lost. And everything crashes for first user and for second user it works fine.

In fact if second user is accessing the same report then first user can see second user's report information which is no at all acceptable. I am sure that should be some way so that multiple users can communicate with reporting server programmatically at the same time. Microsoft wouldn't make reporting web service so that only one user can access report at a time, would they?

Can anyone help me with this problem? ANyhelp will be greatly appreciated.

I don't have anonymous access enabled. I am using integrated windows authentication. Earlier i was trying to authenticate all users with one administartor credentials to communicate with Reporting web service. But then i changed my code so that it takes individual user's credentials. But this didn't make any difference.

I read in one reporting service book that Report server overwrites SessionID member after each call to the Render method. Therefore, two subsequent report requests will share same sessions only if they ask for the same report (assuming that the parameter set is same)

For example, let's say you run report A, then report B, and then report A again. When report B is rendered, its session identifier will overwrite the previous session identifier, which means that you will lose report A's session identifier. When report A is run again, even if the parameter set is the same, its execution will create a new report session and IsNewExecution will return true.

So i think this overwriting of SessionID by report server is causing the problem. But i don't know yet how to program so that it maintains proper SessionID for each user and hence doesn't crash for any user.

If anyone has any idea for solving this problem then please let me know. Thanks in advance.