February 20, 2013 at 12:43 pm
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
February 20, 2013 at 12:53 pm
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
February 20, 2013 at 12:56 pm
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
February 20, 2013 at 1:03 pm
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?
February 20, 2013 at 1:13 pm
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