Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script doesn't seem to function Expand / Collapse
Author
Message
Posted Tuesday, August 21, 2012 3:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 13, 2012 2:19 AM
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
Post #1347659
Posted Tuesday, August 21, 2012 6:45 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:09 AM
Points: 3,864, Visits: 5,007
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”
Post #1347752
Posted Tuesday, August 21, 2012 6:47 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:09 AM
Points: 3,864, Visits: 5,007
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”
Post #1347754
Posted Tuesday, August 21, 2012 7:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:36 AM
Points: 5,233, Visits: 9,469
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
Post #1347769
Posted Tuesday, August 21, 2012 7:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 13, 2012 2:19 AM
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.
Post #1347777
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse