none
Problem executing sp_addsrvrolemember RRS feed

  • Question

  • This is somewhat strange and i am at wits end trying to figure out the root cause.During our database installation process, we execute sp_addsrvrolemember to add a sql login created to the sysadmin server role. We have an install library written in C# using ADO.Net to connect to the database.This C# library performs the operation of executing this stored procedure against the master database.This has been working fine for many builds now on both SQL 2005 & SQL 2008.
    However all of a sudden i am experiencing an error only  on SQL 2008.
    The error reads as follows:Parameter 3 is incorrect for this DBCC statement.
    The strange part is that inspite of the error, the sql login gets successfully added to the sysadmin server role.


    Now here comes the strange part. This was working fine in all of the builds , until we introduced a minor change in the SQL scripts which get executed during our  datbase install. I have verified the scripts and i have not found anything to suggest that these could have caused the issue. The sql scripts do not do anything with the sql logins.

    I have performed the following steps to debug this issue:
    1)Monitored the T-SQL calls during the installation using SQL Profiler. When sp_addsrvrolemember is executed from with the C# library, i experience the error. However if i execute the same T-SQL call using SQL Management Studio. i do not experience this error.
    What could be the issue here?
    Is there anything within our  SQL scripts which creates a condition resulting in this error only on SQL 2008?We are using ADO.Net
    2)I would like to step into the sp_addsrvrolemember  stored procedure as it is invoked from my C# library? Is this possible? If so, how do i do it?
    Any links which could help me. There are tons of links on  the web and i did not seem any of those useful.

    My environment details are as follows:
    Windows 2003 SP2
    SQL Server 2008

    Any help would be very much appreciated.

    Regards,
    Kiran Hegde

    Saturday, September 5, 2009 4:52 AM

All replies

  • Hello Kiran,

     

    Thanks for using Microsoft Forum! This is Ji Zhou and I will be working on the issue with you.

    Firstly, I want to rephrase the issue based on my understanding. If I have misunderstood the issue, please feel free to correct me.

    1.We  do a minor change in a SQL script which is executed when database installing. Before the change, everything works fine.
    2.The error is throwed when sp_addsrvrolemember is executed(monitored from SQL Profiler) and the error message is "Parameter 3 is ..."
    3.Actually, even if we encounter the error, the sp_addsrvrolemember SP executes successfully. The sql login gets successfully.
    4.sp_addsrvrolemember works fine in SSMS. But it has the problem when we execute it from ADO.NET in install script.

    So my understanding is sp_addsrvrolemember is called from the modified SQL script, right? So if we change the SQL script back to the old version, do you encounter the issue?

    Currently, I think there are two possiblities,
    1. The problem happens in sp_addsrvrolemember. If that is the case, based on my research from the internet. There are chances we can observe differents results from ADO.NET and SSMS executing. SSMS by default has the ARITHABORT on, so it reuse the compiled stored procedure from SQL procedure cache. This can cause performance difference between ADO.NET execution and SSMS execution, as well as different results.
    http://stackoverflow.com/questions/834124/ado-net-calling-t-sql-stored-procedure-causes-a-sqltimeoutexception

     

    2.The problem happens right after sp_addsrvrolemember. In that case, it is hard to say where is the problem because we do not the core part of the script. So you may need to post your script context(a reproduciable installer can be better) so that the community here may give some future ideas and help.
     

    Ji Zhou

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Monday, September 7, 2009 7:35 AM
    Moderator
  • So my understanding is sp_addsrvrolemember is called from the modified SQL script, right? So if we change the SQL script back to the old version, do you encounter the issue?

    Well sp_addsrvrolemember is called from ADO.Net(i.e from within the C# library)

    What surprises me is the fact that if the SQL scripts had a problem, then we should have experienced a similar error on a SQL 2005 too. However everything works fine on SQL2005 and the problem is only with SQL2008.

    BTW, do you also know how to step into the stored procedure as it is invoked from C#?
    Monday, September 7, 2009 8:40 AM
  • >>What surprises me is the fact that if the SQL scripts had a problem, then we should have experienced a similar error on a SQL 2005 too. However everything works fine on SQL2005 and the problem is only with SQL2008.

    If the problem is caused by the SP cache, then the problem can be happen from instance to instance. That is to say, it can be independ on the SQL version.

    >>
    BTW, do you also know how to step into the stored procedure as it is invoked from C#?


    To debug into the Stored Procedure, we need open the project property and in the Debug  page, we should check "Enable SQL Server Debugging". There is a KB article on this topic,
    http://support.microsoft.com/kb/316549
    Hope it helps!


    Ji Zhou

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Monday, September 7, 2009 8:49 AM
    Moderator
  • I think i finally figured it out. I have a sql login abc which i am attempting to add as member of the sysadmin server role.
    When i call the C# dll from my installscript project, the string due to some strange reason is being passed to the C# function incorrectly as: "abc\0\0\0\0".

    When sp_addsrvrolemember is invoked using ADO.Net with one of it's parameters  as  abc\0\0\0\0(example: sp_addsrvrolemember  'abc\0\0\0\0','symadmin'), it throws up the error : Parameter 3 is incorrect for this DBCC statement.

    However the same stored procedure invoked directly from SQL Management studio, throws up an error stating that the login 'abc\0\0\0\0' could not be found(Remember the login abc is actually created in the SQL Server)

    This means to say that the behavior of  sp_addsrvrolemember  when executed from ADO.Net and  SQL Management studio is different. Strange huh?
    Can anyone explain if this is indeed true?
    • Marked as answer by Kiran N Hegde Tuesday, September 8, 2009 5:23 AM
    • Unmarked as answer by Kiran N Hegde Tuesday, September 8, 2009 1:53 PM
    Monday, September 7, 2009 12:41 PM
  • Can somone tell me what is it that is different b/w SQL 2005 and SQL 2008 in terms of executing sp_addsrvrolemember from ADO.Net?
    Tuesday, September 8, 2009 1:54 PM
  • Hello Kiran,

    I am not very sure what causes the different for SQL 2005 and SQL 2008 at this time.

    I think if we can figure out why the string is passed to C# function as "abc\0\0\0\0", then we may sniff out some important keywords to do future research.

    So, firstly, let's make a clear clarification about the current issue state. The following is my understand,

    1.When using ADO.NET and SQL Server 2005, the username parameter is passed as "abc", so everything works as expected fine.
    2.When using ADO.NET and SQL Server 2008, the username parameter is passed as "abc\0\0\0\0", it throws an exception "Parameter 3 is incorrect" from ADO.NET, while an different exception message "username could not be found".

    Is my understanding correct? Any mismatch here, please feel free to let me know!

    Then, would you mind providing some future information, especially,
    1. How do we create the username parameter in ADO.NET c# and how do we initialize its value. The whole codes context is prefferable. So I can try to reproduce the same issue in my side and investigate locally.
    2. When you say it is passed as "abc\0\0\0\0", do you mean you get this from SQL Profiler or debugging into the stored procedure according to the KB I posted and see it in Visual Studio watch window


    Best regards,
    Ji Zhou
    MSDN Subscriber Support Team


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, September 10, 2009 5:45 AM
    Moderator
  • The user name value gets passed as "abc\0\0\0\0" for both SQL 2005 & SQL2008.
    With 2005 it works whereas with 2008 it errors out.


    2. When you say it is passed as "abc\0\0\0\0", do you mean you get this from SQL Profiler or debugging into the stored procedure according to the KB I posted and see it in Visual Studio watch window

    In SQL profiler, i see a lot of space being introduced for this parameter. I see the \0\0 characters in the Visual studio watch window.
    I will answer your other question in some time.
    Thursday, September 10, 2009 7:17 AM