Error creating a new user/login.Please help!

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 Administration (2000)

Error creating a new user/login.Please help!

Author

Topic

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-01-07 : 04:14:51

hi,
I have a database : 'harshal'
and want to create a login/user called : 'thisuser'
and want to make him the db_owner for the database.
I am using the following script .

if exists (select * from master.dbo.syslogins where loginname=N'thisuser')
exec sp_droplogin 'thisuser'
if not exists (select * from master.dbo.syslogins where loginname = N'thisuser')
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132)
select @logindb = N'harshal', @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'thisuser', null, @logindb, @loginlang
if not exists (select * from dbo.sysusers where name = N'thisuser' and uid < 16382)
EXEC sp_grantdbaccess N'thisuser', N'harshal'
exec sp_defaultdb 'thisuser','harshal'
exec sp_addrolemember 'thisuser','db_owner'
exec SP_ADDUSER 'thisuser','harshal'
end

but it gives me the following error:

New login created.
Granted database access to 'thisuser'.
Default database changed.
Server: Msg 15014, Level 16, State 1, Procedure sp_addrolemember, Line 37
The role 'thisuser' does not exist in the current database.
Server: Msg 15023, Level 16, State 1, Procedure sp_grantdbaccess, Line 126
User or role 'harshal' already exists in the current database.

any help would be greatly appreciated.
regards,
harhsal.

Expect the UnExpected

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-07 : 07:13:12

You've got the role and username the wrong way round on sp_addrolemember.

sp_grantdbaccess will add the user so you don't want sp_adduser as well.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources