October 13, 2005 at 12:06 pm
First let me say than I am very new to SQL Server, I am a mainframer and the change has been somewhat confusing and frustrating, so please be patient with me. We currently have databases set up on a cluster and at first there were only two databases on it. I am the owner of one of these databases. We now have 20 or more and when I use Enterprise Manager to view the Current Activity I get the message "Server user (user id) is not a valid in database (database name)" Is there a way for me to have access to view processes on my database only? Getting sysadmin rights is not an option for me. Thanks in advance for your input and patience.
October 13, 2005 at 12:54 pm
Not sure what you are looking to see, but try these two things in Query Analyzer:
sp_who -- you can add a user name to this function 'user' to only see their processes...
sp_who2 -- this does not allow the user name, but gives more information.
I wasn't born stupid - I had to study.
October 13, 2005 at 1:30 pm
I tried the both in query analyzer and all I get is the message "The command(s) completed successfully". Am I doing something wrong? I'm hoping to see the Process ID, the status, the Command, and the Wait Type.
October 13, 2005 at 3:31 pm
That is a truly strange result. You typed it into Query Analyzer and hit F5 or the green arrow to run it?
Never seen that result from sp_who. You are connected to your own database?
Hope someone else who has seen this comes across this post.
I wasn't born stupid - I had to study.
October 16, 2005 at 7:19 pm
I have had the same problem reported by a couple of users last month. In the past they have been able to view all connections through EM. Now they get the above message. I have confirmed this using a non privileged account but have not found the cause.
As no SQL service packs/hotfixes have been applied recently I can only assume it is related to an OS patch recently deployed. We run 8.00.818 on the servers in question. OS is Win2003 SP1. I have confirmed a Win2003 SP1 machine with no hotfixes does not report the error, while a Win2003 SP1 with all latest hotfixes does.
My fix was to show the clients sp_who(2) in QA. No errors reported using this interface. I actually added an order by DB clause to make it easier for the client to read. I am sure your DBA could modify it to only report on one DB if this is a requirement.
Given your output from sp_who it may also pay to get the DBA to check the proc. It may have been modified to restrict its use.
Glenn
October 17, 2005 at 11:05 am
You might check that the "sql noexec" option is not turned on in query analyzer. Select tool/options/connection properties. Uncheck the "set noexec" box if it's checked. Having it set will definitely give you the message you're describing.
The noexec option is used to display the planned execution plan without actually running the sql.
Judy
October 19, 2005 at 5:53 pm
You could also run the 'select * from master..sysprocesses' query to find out the processes in all databases.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply