Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Posts
    3

    Unanswered: SUSER_NAME conversion to SUSER_SNAME

    I have hundreds of stored procedures and triggers which use SUSER_NAME to identify the running user. Upgrading from SQL 6.5 to SQL 2000, I see (from experience and from BOL) that SUSER_NAME now always returns NULL, and should be changed to SUSER_SNAME.

    Has anyone seen or developed a way to make this easy? I don't relish modifying and executing several hundred script files.

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Lightbulb Re: SUSER_NAME conversion to SUSER_SNAME

    Originally posted by KABerasley
    I have hundreds of stored procedures and triggers which use SUSER_NAME to identify the running user. Upgrading from SQL 6.5 to SQL 2000, I see (from experience and from BOL) that SUSER_NAME now always returns NULL, and should be changed to SUSER_SNAME.

    Has anyone seen or developed a way to make this easy? I don't relish modifying and executing several hundred script files.
    Q1 Has anyone seen or developed a way to make this easy?
    A1 Not exactly, however:
    I have found it useful to use my own stored procedures (and functions in the case of Sql Server 2k), to avoid or at least minnimize that type of issue. That is, in some migrations from 6.5 to 7.0 to 2k, all I have had to do is recode my stored procedures, (and that way, many things still work), without endless script rev. pain.

  3. #3
    Join Date
    Nov 2002
    Posts
    3
    DBA:

    I'm not sure I follow.

    I tried creating my own SUSER_NAME function which calls the new SUSER_SNAME function. However, an unqualified call to SUSER_NAME still uses the standard system function, which returns null. The only way to use my replacement function is to change all my code to specify the owner of the custom version. That defeats my purpose, since I'm trying to find a way to *not* change all my code.

    I went so far as to create my custom version in the master database as the dbo user. No change to the behavior.

    If they had eliminated the old SUSER_NAME function entirely, this would work fine. But they left a useless form of it in there.

  4. #4
    Join Date
    Oct 2002
    Posts
    369

    Lightbulb

    Originally posted by KABerasley
    DBA: I'm not sure I follow.
    I tried creating my own SUSER_NAME function which calls the new SUSER_SNAME function. However, an unqualified call to SUSER_NAME still uses the standard system function, which returns null. The only way to use my replacement function is to change all my code to specify the owner of the custom version. That defeats my purpose, since I'm trying to find a way to *not* change all my code.
    I went so far as to create my custom version in the master database as the dbo user. No change to the behavior.
    If they had eliminated the old SUSER_NAME function entirely, this would work fine. But they left a useless form of it in there.
    Q1 [I'm not sure I follow. I'm trying to find a way to *not* change all my code. If they had eliminated the old SUSER_NAME function entirely, this would work fine. But they left a useless form of it in there.]

    A1 I understand your predicament all too well, I assure you (been there, suffered that). I agree; this doesn't help your situation at the moment. (Sorry, the only thing I can suggest for the immediate issue is say, using a tool like ER Studio to help reduce the drudgery a little.)

    What I am suggesting is, for example, instead of replacing all references to SUSER_NAME with SUSER_SNAME; => Instead, replace it with something unique that MS is not likely to make a keword anytime soon, say Master.dbo.sp_KABerasley_SUSER_SNAME.

    THEN, in 5 years or so when SUSER_SNAME similarly is rendered worse than useless (and is replaced with say, SSUSER_SSNAME), all you'll have to do is redefine a single function, stored procedure, etc. (in this example, sp_KABerasley_SUSER_SNAME).

  5. #5
    Join Date
    Nov 2002
    Posts
    3
    Thanks for the clarification. I've incorporated your suggestion to recode both SUSER_NAME and SUSER_ID.

  6. #6
    Join Date
    Oct 2002
    Posts
    369

    Lightbulb


    Thanks for the clarification. I've incorporated your suggestion to recode both SUSER_NAME and SUSER_ID.
    You are welcome.

    I've used the strategy with other special system stored procedures and certain xp_ stored procedures as well (sp_Who, etc.).

    Doing so has spared a great deal of Sql Server service pack, and version upgrade "grief" on the systems where it has been implemented. In general, I reccomend implementing the strategy in the environments I work in / with (especially in organizations that perform internal development).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •