
IF NOT EXISTS ( SELECT * FROM master..syslogins WHERE NAME='as')
	CREATE LOGIN [as] WITH PASSWORD= 0x0100DF9DC0B91D68BE01427DA76A37D868364E211480E7212A01 HASHED ,DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
	
exec sp_addsrvrolemember 'as' ,'bulkadmin' 
exec sp_addsrvrolemember 'as' ,'dbcreator' 

DECLARE @DBName NVARCHAR(500)
DECLARE Csr CURSOR LOCAL FAST_FORWARD READ_ONLY FOR  
	SELECT [Name] FROM sys.databases s
	WHERE s.database_id  > 4 AND s.state_desc = 'ONLINE'
OPEN Csr 
FETCH NEXT FROM Csr INTO @DBName
WHILE @@FETCH_STATUS = 0 
BEGIN
	
	EXEC ('USE '+@DBName+' 
			IF NOT EXISTS (SELECT * FROM sysusers
						   WHERE issqlrole = 1 AND NAME=''arian_spexecutor'')
			BEGIN 				
				CREATE ROLE arian_spexecutor 
				GRANT EXECUTE TO arian_spexecutor 
			END
			 
		   IF NOT EXISTS (SELECT * FROM sysusers
						  where  NAME=''as'')
				CREATE USER [as] FOR LOGIN [as]
	 
			EXEC sp_addrolemember N''db_backupoperator'', N''as''
			EXEC sp_addrolemember N''db_datareader'', N''as''
			EXEC sp_addrolemember N''db_datawriter'', N''as''
			EXEC sp_addrolemember N''arian_spexecutor'', N''as''
			EXEC sp_addrolemember N''db_ddladmin'', N''as''
		')
	  	
	FETCH NEXT FROM Csr INTO @DBName
END	
CLOSE Csr 
DEALLOCATE Csr 


EXEC ('USE MASTER 
	  GRANT VIEW SERVER STATE TO [as]')




