SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Script doesn't seem to function


Script doesn't seem to function

Author
Message
Inno
Inno
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 28
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 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
if @ret=0
BEGIN
if (Select Count(*) from sys.sql_logins where [name] = @admin)=0
BEGIN
set @sql2 = 'CREATE LOGIN ' + @admin + ' 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
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, '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)>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 ([email],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
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCrazy Eights
SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)

Group: General Forum Members
Points: 9063 Visits: 7283
Consider including EXECUTE AS in your stored procedure.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCrazy Eights
SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)

Group: General Forum Members
Points: 9063 Visits: 7283
Also, consider using IF (NOT) EXISTS instead of COUNT(*). it is usually much more efficient.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14474 Visits: 15980
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
Inno
Inno
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 28
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search