Sunday, March 11, 2012

creating a new login and user

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.exec [sp_addrolemember] 'db_owner','thisuser':)|||how could I miss that one :(
thanks anyways.
regards,
Harshal.|||looks like u r new to these sp ... can check in online book.

correct code is as following ...

-------
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 'thisuser', 'harshal'
exec sp_addrolemember 'db_owner','thisuser'
end

-------

No comments:

Post a Comment