SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 crosstab doesn't work without sysadmin-rights
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rstork
Starting Member

Germany
1 Posts

Posted - 11/24/2004 :  04:53:50  Show Profile  Reply with Quote
Hello,

I am using the very good crosstab-procedure, but I have problems to run the procedure without sysadmin-rights. Normal Users get the message
"Server: Msg 156, Level 15, State 1, Line 1
Falsche Syntax in der Nähe des END-Schlüsselwortes."

So I tried to debug it in QA. It seems, that normal users can create and drop the temp-table.

The statement that creates the problem is
/* This statement doesn't work with "normal" User, only with sysadmin !! */
SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

The variable @sql contains after execution of this statement with sysadmin-right something like this:
'\\s065301f\swmgmt' = min( CASE ServerShare WHEN '\\s065301f\swmgmt' THEN Drive END), ....

without sysadmin-right it contains only
'\\s065301f\swmgmt' = min(Drive END), ....

Can anyone help, why the statement did not work like expected?

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 11/24/2004 :  12:58:53  Show Profile  Visit nr's Homepage  Reply with Quote
##pivot - good grief.
This is a global temp table so if multiple users use it they will conflict.

I would look at the way this is working - this can probably be changed to a temp table. The only reason for a global temp table would be if it is getting another connection which needs to access it.

\\s065301f\swmgmt' = min(Drive END),
This just looks like it is trying to build a string and has an error in the code.
Is it trying to build a path? Might be why it needs a global temp table but you should find another way.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000