Post reply

MSDE owner user problem

  • June 2, 2005 at 10:59 am

    #66605

    Hi !

    I'm having a little problem with MSDE.

    First, I had a SQL Server DB which owner user is WSREGISTER and all owner tables are WSREGISTER.

    Then, I've dettached this DB and attached it in another server running MSDE.

    Now, even connecting with WSREGISTER user, I'm having problems

    executing SELECT * FROM USERS.

    But SELECT * FROM WSREGISTER.USERS works fine.

    Before, using SQL Server, I could just execute the first query

    (without specifying the user).

    Is this a MSDE limitation ?

    Any suggestion ?

    Thanks in Advance


    Rechousa,
    V.N.Gaia
    Portugal

  • June 2, 2005 at 11:24 am

    #562646

    research sp_changeobjectowner and sp_change_users_login 'Auto_Fix'



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • June 2, 2005 at 11:27 am

    #562648

    Also, this is exactly why you should always specify the object owner. There are other reasons, even more important than this one, but this is one area where it doesn't pay at all to be lazy.

  • June 2, 2005 at 11:54 am

    #562660

    I understand that and I usually do it, so this is a issue, correct ?

    Thanks,


    Rechousa,
    V.N.Gaia
    Portugal

  • June 2, 2005 at 11:57 am

    #562663

    Yes... but AJ already posted the solution. Just open the books online and you'll be able to find the solution.

  • June 2, 2005 at 12:03 pm

    #562668

    Hi !

    I've checked AJ solution, but I didn't like it (sorry AJ ), because I don't want to change all 94 tables user owner and all my Views and SPs and my code.

    So, I think, if there is nothing else I can do, I'll change other applications (just in a few lines of code) to specify the user (eg SELECT * FROM WSREGISTER.USERS)

    Thanks AJ,

    Thanks Remi,


    Rechousa,
    V.N.Gaia
    Portugal

  • June 2, 2005 at 12:10 pm

    #562671

    Takes even less time to run this... or the fix login sp :

    Select 'EXEC sp_changeobjectowner ''' + O.Name + ''', ''WSREGISTER ''' from dbo.SysObjects O where O.Status >= 0 order by XType, Name

    Run in query analyser, paste the results in query analyser and run the script, you're done in 10 seconds.

    EXEC sp_changeobjectowner 'vwPiecesInventaire2005-30Avril', 'WSREGISTER '

    EXEC sp_changeobjectowner 'vwPrixSpeciaux_Unique', 'WSREGISTER '

    EXEC sp_changeobjectowner 'vwRankFacturesParMois', 'WSREGISTER '

    EXEC sp_changeobjectowner 'vwRankTodaysFacturation', 'WSREGISTER '

  • June 2, 2005 at 12:11 pm

    #562673

    BTW I've never actually run that on defaults or constraints but I wouldn't expect much problems to come out of this.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply