Creating user fails. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Creating user fails.

Author

Topic

rkruis
Starting Member

28 Posts

Posted - 2010-11-26 : 17:08:22

I am using a script to create a new user for logging in. When I do in script, the user looks fine in the enterprise manager, but I cannot use his user/pass to log in.

If I create the user, using enterprise manager. I have no problems.

Can someone tell me what I am doing wrong?


if not exists (select * from master.dbo.syslogins where loginname = N'Web')
BEGIN
	declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'Web', @loginlang = N'us_english'
	if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
		select @logindb = N'master'
	if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
		select @loginlang = @@language
	exec sp_addlogin N'Web', null, @logindb, @loginlang
END
GO

if not exists (select * from dbo.sysusers where name = N'Web' and uid < 16382)
	EXEC sp_grantdbaccess N'Web', N'Web'
GO

exec sp_addrolemember N'db_datareader', N'Web'
GO

exec sp_addrolemember N'db_datawriter', N'Web'
GO

exec sp_addrolemember N'db_owner', N'Web'
GO

Error I receive:


Server: Msg 18456, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][Sql Sever] Login failed for user 'Web'

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-26 : 17:13:13

Check the users default database-it is likely master or something the has not been granted access to.

exec master.dbo.sp_defaultdb 'Web',[defaultdbname]

Poor planning on your part does not constitute an emergency on my part.

rkruis
Starting Member

28 Posts

Posted - 2010-11-26 : 17:31:12

I just tried

exec master.dbo.sp_defaultdb 'Web',[Web]

and

exec master.dbo.sp_defaultdb 'Web',[master]

both ended up with the same result. Is there anything I can add that might help resolve this?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-26 : 17:49:38

Are you programmatically setting the password? I presume it isn't NULL as your procedure indicates...

Wrong password would cause the error certainly..

Poor planning on your part does not constitute an emergency on my part.

rkruis
Starting Member

28 Posts

Posted - 2010-11-26 : 18:07:35

I'll admit, I have been working too long today. I want the script to set the user to Web and the password to Web As well.

Where do I set the password here, to be 'Web'

?
exec sp_addlogin N'Web', null, @logindb, @loginlang
to
exec sp_addlogin N'Web', N'Web', @logindb, @loginlang

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-26 : 18:08:30

Yes, the 2nd variable position is the password.

Poor planning on your part does not constitute an emergency on my part.

rkruis
Starting Member

28 Posts

Posted - 2010-11-26 : 18:21:23

That resolved the issue. Thank you so much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources