Stored Procedure Error MSG 15007

  • Msg 15007, Level 16, State 1, Procedure sp_defaultdb, Line 41

    '@LoginString' is not a valid login or you do not have permission.

    Here is the procedure

    CREATE Procedure [dbo].[SetDefaultDatabase]

    @LoginString NVARCHAR(50),

    @DatabaseString NVARCHAR(50)

    As

    Begin

    Set NoCount On

    Exec sp_defaultdb @loginame='@LoginString', @defdb='@DatabaseString'

    End

    GO

    Why would this be generating the message

    Here is the return

    DECLARE@return_value int

    EXEC@return_value = [dbo].[SetDefaultDatabase]

    @LoginString = N'Sample\login',

    @DatabaseString = N'ABC'

    SELECT'Return Value' = @return_value

    GO

  • try taking the single quotes away from around your parameters

    CREATE Procedure [dbo].[SetDefaultDatabase]

    @LoginString NVARCHAR(50),

    @DatabaseString NVARCHAR(50)

    As

    Begin

    Set NoCount On

    Exec sp_defaultdb @loginame=@LoginString, @defdb=@DatabaseString

    End

    GO

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • This maybe?

    CREATE Procedure [dbo].[SetDefaultDatabase]

    @LoginString NVARCHAR(50),

    @DatabaseString NVARCHAR(50)

    As

    Begin

    Set NoCount On

    Exec sp_defaultdb @loginame = @LoginString, @defdb = @DatabaseString

    End

    GO

  • So when I use SSMS to exexute the procedure and I enter the Login it puts a single ' in front of the Sample/abc when I go to enter the second parameter for the database. So it looks like 'Sample\login; it works if I go back up and manually change it! How can I keep out the single ' before the string?

  • This is how it should be executed:

    exec dbo.SetDefaultDatabase @LoginString = '[Sample/abc]', @DatabaseString = 'ThisDatabase';

Viewing 5 posts - 1 through 4 (of 4 total)

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