Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Unanswered: 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.

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    exec [sp_addrolemember] 'db_owner','thisuser'

  3. #3
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Angry

    how could I miss that one
    thanks anyways.
    regards,
    Harshal.

  4. #4
    Join Date
    Jan 2003
    Posts
    15
    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

    --------------------

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •