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