Script doesn't seem to function

  • Hey guys...The script I have...works If I also have to make a login. When my login on the server already exists, then the script isn't functioning any more. I'm trying to use my script to create the admin user on my database. Here's the code:

    USE [Abosystem4_Archiv_test]

    GO

    /****** Object: StoredProcedure [dbo].[ArcivebaseUsers] Script Date: 08/21/2012 11:03:14 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER Procedure [dbo].[ArcivebaseUsers]

    @rolename sysname ,

    @rolepasswd sysname,

    @admin-2 sysname,

    @adminpasswd sysname

    as

    DECLARE @sql1 nvarchar(4000)

    DECLARE @sql2 nvarchar(4000)

    DECLARE @sql3 nvarchar(4000)

    DECLARE @ret int

    --CREATE APPLICATION ROLE dds_arcivebaseadmin WITH PASSWORD = 'crpe32.dll'

    Select @ret = Count(*) from sys.database_principals where [name] = @rolename

    if @ret=0

    BEGIN

    set @sql1 = 'CREATE APPLICATION ROLE ' + @rolename + ' WITH PASSWORD = ''' + @rolepasswd + ''''

    print @sql1

    exec sp_executesql @sql1

    if @@error<>0

    BEGIN

    print 'Anwendungsrolle konnte nicht erstellt werden.'

    return (1)

    END

    END

    --CREATE LOGIN DDS_Admin with Password='crpe32.dll'

    Select @ret = Count(*) from sys.syslogins where [name] = @admin-2

    if @ret=0

    BEGIN

    if (Select Count(*) from sys.sql_logins where [name] = @admin-2)=0

    BEGIN

    set @sql2 = 'CREATE LOGIN ' + @admin-2 + ' with Password=''' + @adminpasswd + ''''

    print @sql2

    exec sp_executesql @sql2

    if @@error <>0

    BEGIN

    print 'Login DDS_Admin konnte nicht erstellt werden.'

    return (1)

    END

    END

    if (Select COUNT(*) from sys.database_principals where [name]=@admin)=0

    BEGIN

    set @sql3 = 'CREATE USER ' + @admin-2

    exec sp_executesql @sql3

    print @sql3

    if @@error <>0

    BEGIN

    print 'Benutzer DDS_Admin konnte nicht erstellt werden.'

    return (1)

    END

    END

    if (select COUNT(*) from users where [login]=@admin)=0

    BEGIN

    print 'Insert statements...'

    Insert into users (username, login, login_until, login_time_start, login_time_end) values (@admin, @admin-2, '01.01.2030','00:00:00','23:59:59')

    Insert into users_groups (id_no_user, id_no_group) Select u.id_no, g.id_no from users u, groups g where g.groupname='Administratoren' and u.login=@admin

    Insert into users_groups (id_no_user, id_no_group) Select u.id_no, g.id_no from users u, groups g where g.groupname='Jeder' and u.login=@admin

    END

    if (Select Count(*) from sys.database_principals where [name] = @admin-2)>0

    BEGIN

    print 'rolemembers'

    exec sp_addrolemember 'db_accessadmin',@admin

    exec sp_addrolemember 'db_securityadmin',@admin

    exec sp_addrolemember 'db_datareader',@admin

    exec sp_addrolemember 'db_datawriter',@admin

    END

    END

    Select @ret = Count(*) from sys.database_principals where [name] = 'dds_arcivebase_user'

    if @ret=0

    BEGIN

    --Erstelle Anwendungsrolle dds_arcivebaseuser mit Rechten

    print 'Create Role...'

    CREATE ROLE dds_arcivebase_user

    if @@error<>0

    BEGIN

    print 'dds_arcivebase_user konnte nicht erstellt werden.'

    return (1)

    END

    END

    Select @ret = Count(*) from sys.database_principals where [name] = 'dds_arcivebase_user'

    if @ret<>0

    BEGIN

    Grant exec on UpdateArchiveRights to dds_arcivebase_user

    Grant select, insert, update on annotations to dds_arcivebase_user

    Grant select, insert, update on archive_doc_attachment to dds_arcivebase_user

    Grant select, insert, update on archive_groups to dds_arcivebase_user

    Grant select, insert, update on archive_right to dds_arcivebase_user

    Grant select on archive_right_search to dds_arcivebase_user

    Grant select on archives_rights_search to dds_arcivebase_user

    Grant select on archive_groups_search to dds_arcivebase_user

    Grant select on [users_archives_search] to dds_arcivebase_user

    Grant select on archives to dds_arcivebase_user

    Grant select, insert, update on arcivebase_rights to dds_arcivebase_user

    Grant select on arcivebase_rights_search to dds_arcivebase_user

    Grant select, insert, update on document_words to dds_arcivebase_user

    Grant select, insert, update on group_rights to dds_arcivebase_user

    Grant select on groups to dds_arcivebase_user

    Grant select on history to dds_arcivebase_user

    Grant select on mediumtypes to dds_arcivebase_user

    Grant select on rights to dds_arcivebase_user

    Grant select on user_groups_search to dds_arcivebase_user

    Grant select on users to dds_arcivebase_user

    Grant select on users_archives to dds_arcivebase_user

    Grant select on users_groups to dds_arcivebase_user

    Grant select on words to dds_arcivebase_user

    grant select on archive_columns_rights to dds_arcivebase_user

    Grant update (,fax, handy, id_no,id_no_last_archive,language, position, tel, zeichen )

    on users to dds_arcivebase_user

    Deny update (login,login_time_end ,login_time_start ,login_until ,pwt_change ,username)

    on users to dds_arcivebase_user

    Deny update on annotations to dds_arcivebase_user

    END

    Select @ret = Count(*) from sys.database_principals where [name] = @rolename -- 'dds_arcivebaseadmin'

    if @ret<>0

    BEGIN

    Grant exec on UpdateArchiveRights to dds_arcivebaseadmin

    Grant exec on Countsearch to dds_arcivebaseadmin

    Grant select, insert, update, delete on annotations to dds_arcivebaseadmin

    Grant select, insert, update, delete on archive_doc_attachment to dds_arcivebaseadmin

    Grant select, insert, update on archive_groups to dds_arcivebaseadmin

    deny delete on archive_groups to dds_arcivebaseadmin

    Grant select, insert, update, delete on archive_paths to dds_arcivebaseadmin

    Grant select, insert, update, delete on archive_right to dds_arcivebaseadmin

    Grant select on archive_right_search to dds_arcivebaseadmin

    Grant select on archives_rights_search to dds_arcivebaseadmin

    Grant select on archive_groups_search to dds_arcivebaseadmin

    Grant select on [users_archives_search] to dds_arcivebaseadmin

    Grant select, insert, update, delete on archives to dds_arcivebaseadmin

    Grant select, insert, update, delete on arcivebase_rights to dds_arcivebaseadmin

    Grant select on arcivebase_rights_search to dds_arcivebaseadmin

    Grant select on db_version to dds_arcivebaseadmin

    Grant select, insert, update, delete on doc_catalog to dds_arcivebaseadmin

    Grant select, insert on doctypes to dds_arcivebaseadmin

    deny update, delete on doctypes to dds_arcivebaseadmin

    Grant select, insert, update, delete on document_words to dds_arcivebaseadmin

    Grant exec on document_words_search to dds_arcivebaseadmin

    Grant select, insert, update, delete on group_rights to dds_arcivebaseadmin

    Grant select, insert, update, delete on groups to dds_arcivebaseadmin

    Grant select, insert, update on history to dds_arcivebaseadmin

    deny delete on history to dds_arcivebaseadmin

    Grant select, insert, update on mediumtypes to dds_arcivebaseadmin

    deny delete on mediumtypes to dds_arcivebaseadmin

    Grant select, insert, update, delete on rights to dds_arcivebaseadmin

    Grant select on user_groups_search to dds_arcivebaseadmin

    Grant select, insert, update, delete on users to dds_arcivebaseadmin

    Grant select on users_archives to dds_arcivebaseadmin

    Grant select, insert, update, delete on users_groups to dds_arcivebaseadmin

    Grant select, insert, update, delete on words to dds_arcivebaseadmin

    Grant select, insert, update on db_version to dds_arcivebaseadmin

    grant select on archive_columns_rights to dds_arcivebaseadmin

    Declare grantArchiveAccess Cursor for

    select tablename

    from archives

    declare @tablenameX varchar(255)

    declare @sqlX nvarchar(1000)

    open grantArchiveAccess

    Fetch next from grantArchiveAccess

    into @tablenameX

    WHILE (@@FETCH_STATUS <> -1)

    BEGIN

    set @sqlX = 'Grant select, insert, update on ' + @tablenameX + ' to dds_arcivebaseadmin'

    --print @sqlX

    exec sp_executesql @sqlX

    Fetch next from grantArchiveAccess

    into @tablenameX

    END

    close grantArchiveAccess

    deallocate grantArchiveAccess

    END

    And the problem that's coming is:

    The Loginname has already an account under another username

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • You need to add extra logic to the CREATE LOGIN section of your script to obtain the SID of the new (or existing) login. Then, in the CREATE USER section, you need to check both that the user name you want to create doesn't already exist, and that there isn't already a user in the database with the SID (certainly shouldn't be if you just created the login).

    John

  • Well...guys...I found my issue after a long day of searching....

    I was trying to create a user from dbo...so that ain't going to work never ever...

    But i kinda like solved my problem...I just added the dbo to the users table....and that kinda like worked..

    Sorry that I'm asking all kind of stupid questions, but I'm a total noob in SQL

    Thanks for the replies, and the time taken.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply