August 21, 2012 at 3:37 am
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
August 21, 2012 at 6:45 am
This was removed by the editor as SPAM
August 21, 2012 at 6:47 am
This was removed by the editor as SPAM
August 21, 2012 at 7:11 am
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
August 21, 2012 at 7:23 am
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